package com.doctor.database;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.HttpStatus;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;

import com.doctor.bean.AdvantageBean;
import com.doctor.bean.AdvantageListBean;
import com.doctor.bean.ArticleBean;
import com.doctor.bean.ArticleListBean;
import com.doctor.bean.BookServiceBean;
import com.doctor.bean.BookServiceListBean;
import com.doctor.bean.ContactsBean;
import com.doctor.bean.DiseaseRecordBean;
import com.doctor.bean.DoctorBean;
import com.doctor.bean.DoctorListBean;
import com.doctor.bean.DownLoadDiseaseRecordBean;
import com.doctor.bean.DownLoadDiseaseRecordBeanList;
import com.doctor.bean.DownLoadPatientBean;
import com.doctor.bean.DownLoadPatientBeanList;
import com.doctor.bean.HealthManagerBean;
import com.doctor.bean.HealthManagerListBean;
import com.doctor.bean.KnowledgeBean;
import com.doctor.bean.KnowledgeListBean;
import com.doctor.bean.OfficeBean;
import com.doctor.bean.PatientFileBean;
import com.doctor.bean.PatientFileListBean;
import com.doctor.bean.UserBean;
import com.doctor.bean.WorkArrangeBean;
import com.doctor.comm.ConstConfig;
import com.doctor.comm.FileHelper;
import com.doctor.comm.LoadingTip;
import com.doctor.comm.MyApplication;
import com.doctor.comm.URLConfig;
import com.doctor.net.NetUtil;
import com.doctor.ui.BaseFragmentActivity;
import com.google.gson.Gson;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Handler;
import android.os.Message;
import android.widget.Toast;

public class DbOperator {
	
	private static DbOperator dbOperator;
	private static Context context;
	private static DbHelper dbHelper;
	private static SQLiteDatabase db;
	//一次查询最多10条数据
	public static final int maxCount=10;
	//运行线程延时查询时间
	public static final long delayTime=500;
	
	private DbOperator(Context context){
		this.context=context;
	}
	
	//获取DbOperator单例
	public static DbOperator getInstance(Context context){
		if (dbOperator==null) {
			dbOperator=new DbOperator(context);
		}
		if (dbHelper==null) {
			dbHelper=new DbHelper(context);
		}
		if (db==null) {
			db = SQLiteDatabase.openDatabase(ConstConfig.DB_FILE+"/"+ConstConfig.DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
			//开启外键约束
			db.execSQL("pragma foreign_keys=on");
		}
		return dbOperator;
	}
	
	/**
	 * 插入一条数据
	 * @param table
	 * @param values
	 * @return
	 */
	public long insertData(String table, ContentValues values){
		long row=db.insert(table, null, values);
		return row;
	}
	
	
	
	/**
	 * 根据id删除一条记录
	 * @param table
	 * @param id
	 * @return
	 */
	public int deleteData(String table, int id){
		int row=db.delete(table, "_id="+id, null);
		return row;
	}
	
	/**
	 * 根据id删除一条记录
	 * @param table
	 * @param id
	 * @return
	 */
	public int deleteData(String table, String id){
		int row=db.delete(table, "_id="+id, null);
		return row;
	}
	
	
	/**
	 * 批量插入医生信息
	 * @param list
	 */
	public void insertDoctorList(DoctorListBean list){
		db.beginTransaction();
		ContentValues values=new ContentValues();
		for (DoctorBean bean : list.getDataList()) {
			values.clear();
			values.put("_cid", bean.getId());
			values.put("_name", bean.getRealname());
			values.put("_sex", bean.getSex());
			values.put("_hospital", bean.getHospital());
			values.put("_birth_day", bean.getBrith());
			values.put("_post_title", bean.getZc());
			values.put("_partment", bean.getKs());
			values.put("_post", bean.getZw());
			values.put("_advantage", bean.getExpertise());
			values.put("_time", bean.getCztime());
			values.put("_address", bean.getAddress());
			values.put("_phone", bean.getTel());
			values.put("_mobile", bean.getMobile());
			values.put("_special_num", bean.getZh());
			values.put("_email", bean.getEmail());
			values.put("_qq", bean.getQq());
			values.put("_blog", bean.getBlog());
			values.put("_fetion", bean.getFx());
			values.put("_webchat", bean.getWx());
			values.put("_other", bean.getRemark());
			values.put("_webSite", bean.getWebsite());
			
			db.insert(ConstConfig.DOCTOR_TABLE, null, values);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}
	
	
	/**
	 * 批量插入健康管理数据
	 * @param list
	 */
	public void insertHealthManagerList(HealthManagerListBean list){
		db.beginTransaction();
		ContentValues values=new ContentValues();
		for (HealthManagerBean bean : list.getDataList()) {
			values.clear();
			String encryptAna = null;
			String encryptInstruct = null;
			try {
				encryptAna=DES.encryptDES(bean.getJkfx(), DES.KEY);
				encryptInstruct=DES.encryptDES(bean.getJkzd(), DES.KEY);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			values.put("_id", bean.getId());
			values.put("_disease", bean.getJb());
			values.put("_health_ana", encryptAna);
			values.put("_health_instruct", encryptInstruct);
			values.put("_health_direct", bean.getJkts());
			db.insert(ConstConfig.HEALTH_MANAGER_TABLE, null, values);
		}
		db.setTransactionSuccessful();
		db.endTransaction();
	}
	
	public interface CategoryCallback{
		public void onSelected(List<KnowledgeBean> list);
	}
	/**
	 * 批量插入医学知识目录并返回指定父id下的子目录
	 * @param beanList
	 */
	public void insertKnowledgeDir(final KnowledgeListBean beanList, final int parentId, final CategoryCallback callback){
		Handler handler=new Handler();
		handler.post(new Runnable() {			
			@Override
			public void run() {
				// TODO Auto-generated method stub
				db.beginTransaction();
				for (KnowledgeBean bean : beanList.getDataList()) {
					db.execSQL("insert into "+ConstConfig.CATEGORT_TABLE+" values("+bean.getId()+","+bean.getPid()+","+"'"+bean.getDirname()+"'"+");");
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				List<KnowledgeBean> list = null;
				if (parentId>=0) {
					list= selectKnowledgeData(parentId);
				}
				callback.onSelected(list);
			}
		});	
	}
	
	
	/**
	 * 下载收藏的文章
	 * @param handler
	 */
	public void downCollectedArticles(final Handler handler){		
		new Thread(){
			public void run() {				
				Cursor idCursor=db.rawQuery("select _id from "+ConstConfig.ARTICLE_TABLE+" union select _id from "+ConstConfig.DELETED_ARTICLE_TABLE, null);
				List<Integer> dupIds=new ArrayList<Integer>();
				if (idCursor!=null && idCursor.getCount()>0) {
					idCursor.moveToFirst();
					do {
						int dupId=idCursor.getInt(0);
						dupIds.add(dupId);
					} while (idCursor.moveToNext());
				}
				idCursor.close();
				//开始下载医学文章
				ContentValues values=new ContentValues();
				int count=0;
				int page=1;
				outLoop:
				while (page<Integer.MAX_VALUE) {
					try {
						String url = URLConfig.COLLECTED_ARTICLE_URL+MyApplication.getInstance().getUserId()+"&page="+page;						
						HttpPost post = new HttpPost(url);
						DefaultHttpClient client = new DefaultHttpClient();
						
						HttpResponse response = client.execute(post);
						Message msg=handler.obtainMessage();
						if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
							HttpEntity entity= response.getEntity();
							String result = EntityUtils.toString(entity);
							if (result==null) {
								LoadingTip.hideDialog();
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							result=result.trim();
							if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
								LoadingTip.hideDialog();
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							Gson gson=new Gson();
							ArticleListBean list=gson.fromJson(result, ArticleListBean.class);
							if (list==null || list.getSize()<=0) {
								LoadingTip.hideDialog();
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							//采用事务往数据库表里批量插入医学文章
							db.beginTransaction();
							for (ArticleBean bean : list.getDataList()) {
								if (dupIds.contains(bean.getId())) {
									//现有或已删除的文章不插入
									continue;
								}
								String encryptJj=null;
								String encryptContent = null;
								String jj=bean.getJj();
								String content=bean.getContent();
								try {
									encryptJj=DES.encryptDES(jj, DES.KEY);
									encryptContent=DES.encryptDES(content, DES.KEY);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								
								int dir=bean.getDir();
								if (dir==54 || dir==55) {
									//下载视频文件
									boolean downVideoResult = downVideo(jj);
									if (!downVideoResult) {
										//视频文件下载失败则不予存入数据库中
										break outLoop;
									}
									
								} else {
									//从content提取图片url依次下载图片
									boolean downImgResult = downImg(content);
									if (!downImgResult) {
										break outLoop;
									}
									
								}
								//万一有重复则忽略插入
								db.execSQL("insert or ignore into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
										bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
										bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+");");
								count++;
							}
							db.setTransactionSuccessful();
							db.endTransaction();
							page++;							
						} else {
							LoadingTip.hideDialog();
							msg.arg1=ConstConfig.FAILED;
							msg.arg2=count;
							handler.sendMessage(msg);
							break;
						}
					} catch (ClientProtocolException e) {
						e.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}	
			}
		}.start();
	}
	
	
	
	/**
	 * 插入医学知识文章(下载收藏的文章)已废弃
	 * @param beanList
	 */
	public void insertCollectedArticle(final ArticleListBean beanList){
		Handler handler=new Handler();
		handler.post(new Runnable() {			
			@Override
			public void run() {
				// TODO Auto-generated method stub
				StringBuilder builder=new StringBuilder();
				builder.append("(");
				for(int i=0; i<beanList.getSize(); i++){
					int id=beanList.getDataList().get(i).getId();
					if (i<beanList.getSize()-1) {
						builder.append(id+",");
					} else if (i==beanList.getSize()-1) {
						builder.append(id+"");
					}
				}
				builder.append(")");
				Cursor idCursor=db.rawQuery("select _id from "+ConstConfig.ARTICLE_TABLE+" where _id in "+builder.toString()
						+" union select _id from "+ConstConfig.DELETED_ARTICLE_TABLE, null);
				List<Integer> dupIds=new ArrayList<Integer>();
				if (idCursor!=null && idCursor.getCount()>0) {
					idCursor.moveToFirst();
					do {
						int dupId=idCursor.getInt(0);
						dupIds.add(dupId);
					} while (idCursor.moveToNext());
				}
				idCursor.close();
				int count=0;
				db.beginTransaction();
				for (ArticleBean bean : beanList.getDataList()) {
					if (dupIds.contains(bean.getId())) {
						//有重复的id则退出本次循环不插入数据
						continue;
					}
					String encryptJj=null;
					String encryptContent = null;
					try {
						encryptJj=DES.encryptDES(bean.getJj(), DES.KEY);
						encryptContent=DES.encryptDES(bean.getContent(), DES.KEY);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					db.execSQL("insert into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
							bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
							bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+");");
					count++;
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				LoadingTip.hideDialog();
				Toast.makeText(context, "下载收藏成功，本次更新"+count+"篇医学文章！", Toast.LENGTH_SHORT).show();
			}
		});	
	}
	
	
	
	
	/**
	 * 插入医学知识文章(APP自带的免费文章)
	 * @param beanList
	 */
	public void insertFreeArticle(final ArticleListBean beanList){
		Handler handler=new Handler();
		handler.post(new Runnable() {			
			@Override
			public void run() {
				// TODO Auto-generated method stub
				db.beginTransaction();
				for (ArticleBean bean : beanList.getDataList()) {
					String encryptJj=null;
					String encryptContent = null;
					try {
						encryptJj=DES.encryptDES(bean.getJj(), DES.KEY);
						encryptContent=DES.encryptDES(bean.getContent(), DES.KEY);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					db.execSQL("insert into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
							bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
							bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+");");
				}
				db.setTransactionSuccessful();
				db.endTransaction();
				LoadingTip.hideDialog();
			}
		});	
	}	
	
	/**
	 * 根据父id查找医学知识目录
	 * @param parentId
	 * @return
	 */
	public List<KnowledgeBean> selectKnowledgeData(int parentId){
		List<KnowledgeBean> beanList=new ArrayList<KnowledgeBean>();
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.CATEGORT_TABLE+" where _parent_id="+parentId+" order by _id asc", null);
		//临床儿科、皮肤栏目
		KnowledgeBean childrenBean = null;
		KnowledgeBean skinBean = null;
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				boolean isNew = cursor.getInt(3) == 1 ? true : false;
				KnowledgeBean bean=new KnowledgeBean(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), isNew);
				if (bean.getId()==6189) {
					//皮肤栏目
					skinBean=bean;
				} else {
					beanList.add(bean);
					if (bean.getId()==28) {
						//临床儿科栏目
						childrenBean=bean;
					}
				}				
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		
		if (childrenBean!=null && beanList.contains(childrenBean) && skinBean!=null) {
			int childrenBeanIndex= beanList.indexOf(childrenBean);
			if (childrenBeanIndex<beanList.size()-1) {
				//皮肤栏目紧跟儿科栏目后面		
				beanList.add(childrenBeanIndex+1, skinBean);
			}
		} else if (skinBean!=null) {
			beanList.add(skinBean);
		}
		return beanList;
	}
	
	/**
	 * 根据父id查找下面两级目录
	 * @param parentId
	 * @return
	 */
	public List<List<KnowledgeBean>> selectDirs(int parentId){
		LinkedHashMap<Integer, List<KnowledgeBean>> map = null;
		StringBuilder sb = null;
		db.beginTransaction();
		Cursor cursor1=db.rawQuery("select * from "+ConstConfig.CATEGORT_TABLE+" where _parent_id="+parentId+" order by _id asc", null);
		if (cursor1 != null && cursor1.getCount() > 0 && cursor1.moveToFirst()) {
			sb = new StringBuilder();
			sb.append("(");
			map = new LinkedHashMap<>();
			do {
				boolean isNew = cursor1.getInt(3) == 1 ? true : false;
				int id = cursor1.getInt(0);
				KnowledgeBean bean=new KnowledgeBean(id, cursor1.getInt(1), cursor1.getString(2), isNew);
				sb.append(id);
				sb.append(",");
				ArrayList<KnowledgeBean> dirs = new ArrayList<>();
				dirs.add(bean);
				map.put(id, dirs);
			} while (cursor1.moveToNext());
			sb.deleteCharAt(sb.length() - 1);
			sb.append(")");
		}
		if (cursor1 != null) {
			cursor1.close();
		}
		
		if (map != null && !map.isEmpty()) {
			Cursor cursor2 = db.rawQuery("select * from "+ConstConfig.CATEGORT_TABLE+" where _parent_id in " + sb.toString() + " order by _parent_id asc,_id asc", null);
			if (cursor2 != null && cursor2.getCount() > 0 && cursor2.moveToFirst()) {
				do {
					boolean isNew = cursor2.getInt(3) == 1 ? true : false;
					KnowledgeBean bean2 = new KnowledgeBean(cursor2.getInt(0), cursor2.getInt(1), cursor2.getString(2), isNew);
					map.get(bean2.getPid()).add(bean2);
				} while (cursor2.moveToNext());
			}
			if (cursor2 != null) {
				cursor2.close();
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		
		ArrayList<List<KnowledgeBean>> dirs = null;
		if (map != null && !map.isEmpty()) {
			dirs = new ArrayList<>();
			for (Map.Entry<Integer, List<KnowledgeBean>> entry : map.entrySet()) {
				dirs.add(entry.getValue());
			}
		}
		return dirs;
	}
	
	/**
	 * 根据id查询二级和三级目录
	 * @param parentId
	 * @return
	 */
	public List<List<KnowledgeBean>> selectSecThirdDir(int parentId){
		db.beginTransaction();
		List<KnowledgeBean> second=selectKnowledgeData(parentId);
		List<List<KnowledgeBean>> secAndThird=new ArrayList<List<KnowledgeBean>>();
		if (second!=null && second.size()>0) {
			for (KnowledgeBean bean : second) {
				List<KnowledgeBean> third1=new ArrayList<KnowledgeBean>();
				third1.add(bean);
				List<KnowledgeBean> third2=selectKnowledgeData(bean.getId());
				if (third2!=null && third2.size()>0) {
					third1.addAll(third2);
				}
				secAndThird.add(third1);
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		return secAndThird;
	}
	

	/**
	 * 根据目录id查找其下的文章id和title
	 * @param id 父目录id
	 * @param dirList 子目录
	 * @return
	 */
	public List<List<ArticleBean>> selectArticleNames(int id, List<KnowledgeBean> dirList){
		List<List<ArticleBean>> beanList= new LinkedList<List<ArticleBean>>();
		StringBuilder sb = new StringBuilder();
		sb.append("(");
		List<Integer> dirIds = new ArrayList<>();
		if (dirList != null && dirList.size() > 0) {
			for (KnowledgeBean dir : dirList) {
				int dirId = dir.getId();
				sb.append(dirId);
				sb.append(",");
				dirIds.add(dirId);
				beanList.add(new LinkedList<ArticleBean>());
			}
		}
		sb.append(id);
		sb.append(")");
		dirIds.add(id);
		beanList.add(new LinkedList<ArticleBean>());
		
		Cursor cursor=db.rawQuery("select _id,_title,_jj,_is_new,_dir from "+ConstConfig.ARTICLE_TABLE+" where _dir in " + sb.toString() + " order by _orderid asc, _id desc", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			int dir, index;
			do {
				ArticleBean bean=new ArticleBean();
				bean.setId(cursor.getInt(0));
				bean.setTitle(cursor.getString(1));
				bean.setJj(cursor.getString(2));
				boolean isNew = cursor.getInt(3) == 1 ? true : false;
				if (isNew) {
					bean.setIsNew(true);
				}
				dir = cursor.getInt(4);
				index = dirIds.indexOf(dir);
				if (index >= 0) {
					beanList.get(index).add(bean);
				}
				
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return beanList;
	}	
	
	
	/**
	 * 搜索医学知识文章
	 * @param keyWord
	 */
	public List<ArticleBean> searchArticles(final String keyWord, final Context context){
		List<ArticleBean> beanList = null;
		Cursor cursor=db.rawQuery("select _id,_title,_dir,_jj,_is_new from "+ConstConfig.ARTICLE_TABLE+" where _title like "+"'%"+keyWord+"%'", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			beanList = new ArrayList<ArticleBean>();
			do {
				ArticleBean bean=new ArticleBean();
				bean.setId(cursor.getInt(0));
				bean.setTitle(cursor.getString(1));
				bean.setDir(cursor.getInt(2));
				boolean isNew = cursor.getInt(3) == 1 ? true : false;
				if (isNew) {
					bean.setIsNew(true);
				}
				
				String jj=cursor.getString(3);
				if (jj!=null && !"".equals(jj)) {
					try {
						jj=DES.decryptDES(jj, DES.KEY);
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
					if (jj.toLowerCase().startsWith("/upload/")) {
						bean.setJj(jj);
					}
				}
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		
		return beanList;
	}	
	
	
	
	
	public interface DirAndArticleCallback{
		public void onDirAndArticle(int dirSize,List<KnowledgeBean> dirList,List<List<ArticleBean>> artList);
	}
	/**
	 * 根据id查找子目录和文章列表
	 * @param id
	 * @param callback
	 */
	public void selectDirAndArticle(int id, DirAndArticleCallback callback){
		List<KnowledgeBean> dirList=selectKnowledgeData(id);
		db.beginTransaction();
		int dirSize = dirList == null ? 0 : dirList.size();
		List<List<ArticleBean>> artList = selectArticleNames(id, dirList);
		db.setTransactionSuccessful();
		db.endTransaction();
		callback.onDirAndArticle(dirSize, dirList, artList);
	}
	
	
	
	//根据表名查询无纸办公模块的数据返回所有字段
	public List<OfficeBean> selectOfficeData(String tableName,int fromRow){
		List<OfficeBean> beanList = null;
		//按照日期、时间、id降序排列，如果是今日要事表则当日的内容放在最前
		Cursor cursor=null;
		if (ConstConfig.TODAY_EVENT_TABLE.equals(tableName)) {
			Calendar calendar=Calendar.getInstance();
			int year=calendar.get(Calendar.YEAR);
			int month=calendar.get(Calendar.MONTH)+1;
			int day=calendar.get(Calendar.DAY_OF_MONTH);
			String m;
			String d;
			if (month<10) {
				m="0"+month;
			} else {
				m=month+"";
			}
			if (day<10) {
				d="0"+day;
			} else {
				d=day+"";
			}
			
			String nowDate=year+"-"+m+"-"+d;
			
			cursor=db.rawQuery("select *,0 as day from "+tableName+" where _remind_date="+"'"+nowDate+"'"
			+" union select *,1 as day from "+tableName+" where _remind_date<>"+"'"+nowDate+"'"+" or _remind_date is null"
			+" order by day asc,_remind_date desc,_remind_time desc,_id desc"+" limit "+maxCount+" offset "+fromRow, null);
		} else {
			cursor=db.rawQuery("select * from "+tableName+" order by _remind_date desc,_remind_time desc,_id desc limit "+maxCount+" offset "+fromRow,null);
		}			
		if (cursor.moveToFirst()) {
			beanList=new ArrayList<OfficeBean>();
			do {
				OfficeBean bean=new OfficeBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
					cursor.getString(4), cursor.getString(5), cursor.getString(6));
				beanList.add(bean);					
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		
		return beanList;
	}
	
	//根据起始行号查找医生信息
	public List<DoctorBean> selectDoctorData(int fromRow, String province, String city, String county){
		Cursor cursor = null;
		if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0 && county != null && county.trim().length() > 0) {
			cursor = db.rawQuery("select * from " + ConstConfig.DOCTOR_TABLE + " where (_cid<>-1 and _province=? and _city=? and _county=?) or _cid=-1"
		          + " order by _advantage asc"+" limit "+maxCount+" offset "+fromRow, new String[]{province, city, county});
		} else {
			cursor = db.rawQuery("select * from " + ConstConfig.DOCTOR_TABLE + " where _cid=-1" + " order by _advantage asc" 
		          + " limit "+maxCount+" offset "+fromRow, null);
		}
		
		List<DoctorBean> beanList=null;
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			beanList=new ArrayList<DoctorBean>();
			do {
				DoctorBean bean=new DoctorBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
						cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getInt(8), cursor.getString(9), cursor.getString(10),
						cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getString(14), cursor.getString(15), cursor.getString(16),
						cursor.getString(17), cursor.getString(18), cursor.getString(19), cursor.getInt(20), cursor.getString(21));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return beanList;
	}
	
	/**
	 * 根据表名和id查找image路径
	 * @param tableName
	 * @param id
	 * @return
	 */
	public String selectImages(String tableName,int id){
		String images = null;
		Cursor cursor=db.rawQuery("select _image from "+tableName+" where _id="+id,null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			images=cursor.getString(cursor.getColumnIndex("_image"));
		}
		cursor.close();
		return images;
	}
	
	//获取提醒数字
	public int selectTipCount(String nowDay,String nowTime){
		int sum=0;
		final String[] tables={ConstConfig.TODAY_EVENT_TABLE,ConstConfig.SCHEDULE_TIME_TABLE,ConstConfig.MEDICAL_INFO_TABLE,ConstConfig.DRUGS_INFO_TABLE,
				ConstConfig.IMMEDIATE_NOTES_TABLE,ConstConfig.FILE_STORAGE_TABLE,ConstConfig.ACCOUNT_BOOK_TABLE};
		db.beginTransaction();
		for (String table : tables) {
			Cursor cursor=db.rawQuery("select count(_remind_time) from "+table+" where _remind_date=? and  _remind_time>?",new String[]{nowDay,nowTime});
					if (cursor!=null && cursor.getCount()>0) {
						cursor.moveToFirst();
						int count=cursor.getInt(cursor.getColumnIndex("count(_remind_time)"));
						sum+=count;
					}
					cursor.close();
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		return sum;
	}
	
	//更新一条数据
	public int updateData(String tableName, ContentValues values, int id) {
		int rows = db.update(tableName, values, "_id=" + id, null);
		return rows;
	}
	
	//更新一条数据
	public int updateData(String tableName, ContentValues values, String id) {
		int rows = db.update(tableName, values, "_id=?", new String[]{id});
		return rows;
	}
		
	/**
	 * 更新数据升级记录
	 * @param colName 字段名
	 * @return
	 */
	public void updateUpdateRecord(String colName, int count){		
		Cursor cursor=db.rawQuery("select "+colName+" from "+ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
		cursor.moveToFirst();
		int original=cursor.getInt(0);
		cursor.close();
		ContentValues values=new ContentValues();
		values.put(colName, original+count);
		db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, null, null);
	}
		
	
	//查询记录的最大id值
	public int getMaxId(String tableName){
		int maxId=-1;
		Cursor cursor=db.rawQuery("select max(_id) from "+tableName, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			maxId=cursor.getInt(cursor.getColumnIndex("max(_id)"));
		}
		if (cursor != null) {
			cursor.close();
		}
		
		return maxId;
	}
	
	/**
	 * 根据表名查找闹钟的最大id
	 * @param table
	 * @return
	 */
	public int getAlarmMaxId(String table){
		int maxId = 0;
		Cursor cursor=db.rawQuery("select max(_alarm_id) from "+table, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			maxId=cursor.getInt(cursor.getColumnIndex("max(_alarm_id)"));
		}
		cursor.close();
		return maxId;
	}
	
	/**
	 * 根据表名和记录id查找闹钟id
	 * @param table
	 * @param id
	 * @return
	 */
	public int getAlarmIdById(String table, int id){
		int alarmId=-1;
		Cursor cursor=db.rawQuery("select _alarm_id from "+table+" where _id="+id, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			alarmId=cursor.getInt(cursor.getColumnIndex("_alarm_id"));
		}
		cursor.close();
		return alarmId;
	}
	
	//查询患者档案
	public List<PatientFileBean> selectPatientFileData(int fromRow){
		List<PatientFileBean> beanList = null;
		Cursor cursor=db.rawQuery(" select * from "+ConstConfig.PATIENT_FILE_TABLE+" order by _id desc limit "+maxCount+" offset "+fromRow, null);
		if (cursor.moveToFirst()) {
			beanList=new ArrayList<PatientFileBean>();
			do {
				PatientFileBean bean=new PatientFileBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
						cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9),
						cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getInt(14));
				bean.setMarriage(cursor.getString(16));
				bean.setContacts(cursor.getString(17));
				bean.setContactsMobile(cursor.getString(18));
				bean.setAllergy(cursor.getString(19));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return beanList;
	}
	

	/**
	 * 获取本地数据库中有效医学文章的最大id(包含未删除及已删除的)
	 * @return
	 */
	public int getArticleMaxId() {
		int maxId = -1;
		
		Cursor cursor = db.rawQuery(" select MAX(_id) from " + ConstConfig.ARTICLE_TABLE + " where _dir not in (54,55) ", null);
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			maxId = cursor.getInt(0);
		}
		if (cursor != null) {
			cursor.close();
		}
		
		Cursor cursor2 = db.rawQuery(" select MAX(_id) from " + ConstConfig.DELETED_ARTICLE_TABLE + " where _dir not in (54,55) ", null);
		if (cursor2 != null && cursor2.getCount() > 0 && cursor2.moveToFirst()) {
			int deletedMaxId = cursor2.getInt(0);
			if (maxId < deletedMaxId)
				maxId = deletedMaxId;
		}
		if (cursor2 != null) {
			cursor2.close();
		}
		
		return maxId;
	}
	
	
	/**
	 * 获取本地数据库中有效医学视频的最大id(包含未删除及已删除的)
	 * @return
	 */
	public int getVideoMaxId() {
		int maxId = -1;
		
		Cursor cursor = db.rawQuery(" select MAX(_id) from " + ConstConfig.ARTICLE_TABLE + " where _dir in (54,55) ", null);
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			maxId = cursor.getInt(0);
		}
		if (cursor != null) {
			cursor.close();
		}
		
		Cursor cursor2 = db.rawQuery(" select MAX(_id) from " + ConstConfig.DELETED_ARTICLE_TABLE + " where _dir in (54,55) ", null);
		if (cursor2 != null && cursor2.getCount() > 0 && cursor2.moveToFirst()) {
			int deletedMaxId = cursor2.getInt(0);
			if (maxId < deletedMaxId)
				maxId = deletedMaxId;
		}
		if (cursor2 != null) {
			cursor2.close();
		}
		
		return maxId;
	}
	

	/**
	 * 查询患者档案
	 * @param id 患者档案id
	 * @return
	 */
	public PatientFileBean getPatientFile(int id){
		PatientFileBean bean = null;
		Cursor cursor=db.rawQuery("select * from " + ConstConfig.PATIENT_FILE_TABLE + " where _id=?", new String[]{String.valueOf(id)});
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
				bean = new PatientFileBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
						cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9),
						cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getInt(14));
				bean.setMarriage(cursor.getString(16));
				bean.setContacts(cursor.getString(17));
				bean.setContactsMobile(cursor.getString(18));
				bean.setAllergy(cursor.getString(19));
				
		}
		if (cursor != null) {
			cursor.close();
		}
		return bean;
	}	
	
	//查询所有的患者名字
	public List<PatientFileBean> selectAllPatientName(){
		List<PatientFileBean> beanList = null;
		Cursor cursor = db.rawQuery("select _id, _name from "+ ConstConfig.PATIENT_FILE_TABLE, null);
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			beanList = new ArrayList<PatientFileBean>();
			do {
				PatientFileBean bean=new PatientFileBean();
				bean.setId(cursor.getInt(0));
				bean.setName(cursor.getString(1));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return beanList;
	}
	
	
	/**
	 * 查询病历资料
	 * @param patientId 患者id
	 * @param fromRow 从第几行开始
	 * @return
	 */
	public List<DiseaseRecordBean> selectDiseaseRecordData(int patientId){
		List<DiseaseRecordBean> beanList=null;
		Cursor cursor=db.rawQuery(" select * from "+ConstConfig.DISEASE_RECORD_TABLE+" where _patient_id="+patientId+" order by _id desc", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			beanList=new ArrayList<DiseaseRecordBean>();
			do {
				DiseaseRecordBean bean=new DiseaseRecordBean(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
						cursor.getString(5), cursor.getString(6), cursor.getString(7),cursor.getString(8),cursor.getString(9),cursor.getString(10),
						cursor.getString(11),cursor.getInt(12),cursor.getString(13),cursor.getString(14),cursor.getString(15), cursor.getInt(16));
				bean.setTime(cursor.getString(18));
				bean.setComplain(cursor.getString(19));
				bean.setPresent(cursor.getString(20));
				bean.setPhysicalCheck(cursor.getString(21));
				bean.setAuxiliaryCheck(cursor.getString(22));
				bean.setInitialDiagnosis(cursor.getString(23));
				bean.setPrescription(cursor.getString(24));
				bean.setDoctorAdvice(cursor.getString(25));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		
		if (cursor != null) {
			cursor.close();
		}
		return beanList;
	}

	
	/**
	 * 查询病历资料
	 * @param id 病历id
	 * @return
	 */
	public DiseaseRecordBean getDiseaseRecord(int id){
		DiseaseRecordBean bean = null;
		Cursor cursor = db.rawQuery("select * from " + ConstConfig.DISEASE_RECORD_TABLE + " where _id=?", 
				new String[]{String.valueOf(id)});
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
				bean = new DiseaseRecordBean(cursor.getInt(0), cursor.getInt(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
						cursor.getString(5), cursor.getString(6), cursor.getString(7),cursor.getString(8),cursor.getString(9),cursor.getString(10),
						cursor.getString(11),cursor.getInt(12),cursor.getString(13),cursor.getString(14),cursor.getString(15), cursor.getInt(16));
				bean.setTime(cursor.getString(18));
				bean.setComplain(cursor.getString(19));
				bean.setPresent(cursor.getString(20));
				bean.setPhysicalCheck(cursor.getString(21));
				bean.setAuxiliaryCheck(cursor.getString(22));
				bean.setInitialDiagnosis(cursor.getString(23));
				bean.setPrescription(cursor.getString(24));
				bean.setDoctorAdvice(cursor.getString(25));
		}
		if (cursor != null) {
			cursor.close();
		}
		return bean;
	}	
	
	
	/**
	 * 查询所有的病历疾病名
	 * @param patientId 患者档案id
	 * @return
	 */
	public List<DiseaseRecordBean> selectAllDiseaseName(int patientId){
		List<DiseaseRecordBean> beanList = null;
		Cursor cursor = db.rawQuery("select _id, _complain from "+ ConstConfig.DISEASE_RECORD_TABLE + " where _patient_id=?", 
				new String[]{String.valueOf(patientId)});
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			beanList = new ArrayList<DiseaseRecordBean>();
			do {
				DiseaseRecordBean bean = new DiseaseRecordBean();
				bean.setId(cursor.getInt(0));
				bean.setComplain(cursor.getString(1));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		if (cursor != null) {
			cursor.close();
		}
		return beanList;
	}	
	
	
	/**
	 * 查询病历下的健康管理
	 * @param id 病历id
	 * @return
	 */
	public DiseaseRecordBean getDiseaseRecordHealthManagerById(int id) {
		DiseaseRecordBean bean = null;
		Cursor cursor = db.rawQuery("SELECT _health_ana,_health_instruct,_health_direct,_remind_time,_cid from " + ConstConfig.DISEASE_RECORD_TABLE + 
				" WHERE _id=?", new String[]{String.valueOf(id)});
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst();
			bean = new DiseaseRecordBean();
			bean.setHealthAna(cursor.getString(0));
			bean.setHealthInstruct(cursor.getString(1));
			bean.setHealthDirect(cursor.getString(2));
			bean.setRemindTime(cursor.getString(3));
			bean.setCid(cursor.getInt(4));
		}
		if (cursor != null) {
			cursor.close();
		}
		return bean;
	}
	
	/**
	 * 获取患者名下的病历数量
	 * @param patientId
	 * @return
	 */
	public int selectDiseaseCount(int patientId){
		int count=-1;
		Cursor cursor=db.rawQuery("select count(_patient_id) from "+ConstConfig.DISEASE_RECORD_TABLE+" where _patient_id="+patientId, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			count=cursor.getInt(cursor.getColumnIndex("count(_patient_id)"));
		}
		cursor.close();
		return count;
	}
	
	/**
	 * 根据表名获取表记录数量
	 * @param table
	 * @return
	 */
	public int getCount(String table){
		int count=-1;
		Cursor cursor=db.rawQuery("select count(_id) from "+table, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			count=cursor.getInt(cursor.getColumnIndex("count(_id)"));
		}
		cursor.close();
		return count;
	}
	
	
	/**
	 * 获取数据升级记录表的已升级数量
	 */
	public List<Integer> getUpdateCount(){
		List<Integer> list=new ArrayList<Integer>();
		Cursor cursor=db.rawQuery("select _articleCount,_videoCount from "+ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
		cursor.moveToFirst();
		list.add(cursor.getInt(0));
		list.add(cursor.getInt(1));
		cursor.close();
		return list;
	}
	
	
	
	/**
	 * 查询非免费或免费文章数量
	 * @return
	 */
	public int getCollectedArticleCount(int isFree){
		int count=-1;
		Cursor cursor=db.rawQuery("select count(_id) from "+ConstConfig.ARTICLE_TABLE+" where _is_free="+isFree, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			count=cursor.getInt(cursor.getColumnIndex("count(_id)"));
		}
		cursor.close();
		return count;
	}
	
	/**
	 * 获取日程安排数据
	 * @param year
	 * @param month
	 * @return
	 */
	public Map<Integer, WorkArrangeBean> selectWorkArrangeData(int year, int month){
		Map<Integer, WorkArrangeBean> map=new HashMap<Integer, WorkArrangeBean>();
		Cursor cursor=db.rawQuery("select _id,_date,_arrange,_year,_month,_day from "+ConstConfig.WORK_ARRANGE_TABLE
				+" where _year="+year+" and _month="+month, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				WorkArrangeBean bean=new WorkArrangeBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2),
						cursor.getInt(3), cursor.getInt(4),cursor.getInt(5));
				map.put(bean.getDay(), bean);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return map;
	}
	
	/**
	 * 根据id查找医学知识文章
	 * @param id
	 * @return
	 */
	public ArticleBean selectArticle(int id){
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.ARTICLE_TABLE+" where _id="+id, null);
		ArticleBean bean = null;
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			String decryptContent = null;
			try {
				decryptContent=DES.decryptDES(cursor.getString(4), DES.KEY);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			bean=new ArticleBean(cursor.getInt(0),cursor.getString(1), cursor.getString(2), cursor.getInt(3),
					decryptContent, cursor.getString(5), cursor.getInt(6), cursor.getString(7), cursor.getInt(8),
					cursor.getString(9), cursor.getInt(10), cursor.getInt(11));
		}
		cursor.close();
		return bean;
	}
	
	
	/**
	 * 根据电话或手机获取患者档案
	 * @param phone
	 * @return
	 */
	public PatientFileBean getPatientFileByPhone(String phone){
		PatientFileBean bean=null;
		if (phone==null || "".equals(phone.trim())) {
			return null;
		}
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.PATIENT_FILE_TABLE+" where _phone="+"'"+phone+"'"+" or _mobile="+"'"+phone+"'", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			bean=new PatientFileBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
					cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9),
					cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13));
			bean.setMarriage(cursor.getString(16));
			bean.setContacts(cursor.getString(17));
			bean.setContactsMobile(cursor.getString(18));
			bean.setAllergy(cursor.getString(19));
		}
		cursor.close();
		return bean;
	}
	
	
	/**
	 * 根据电话或手机获取医生档案
	 * @param phone
	 * @return
	 */
	public DoctorBean getDoctorByPhone(String phone){
		DoctorBean bean = null;
		if (phone==null || "".equals(phone.trim())) {
			return bean;
		}
		Cursor cursor;
	
		String province = null, city = null, county = null;
		String[] region = selectUserRegion();
		if (region != null) {
			province = region[0] != null && region[0].trim().length() > 0 ? region[0] : null;
			city = region[1] != null && region[1].trim().length() > 0 ? region[1] : null;
			county = region[2] != null && region[2].trim().length() > 0 ? region[2] : null;
		}
		
		
		if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0 && county != null && county.trim().length() > 0) {
			cursor=db.rawQuery("select * from " + ConstConfig.DOCTOR_TABLE + " where ((_cid<>-1 and _province=? and _city=? and _county=?) or _cid=-1) and (_phone="+"'"+phone+"'"+" or _mobile="+"'"+phone+"'"+" or _special_num="+"'"+phone+"')",
					new String[]{province, city, county});
		} else {
			cursor=db.rawQuery("select * from "+ConstConfig.DOCTOR_TABLE+" where _cid=-1 and (_phone="+"'"+phone+"'"+" or _mobile="+"'"+phone+"'"+" or _special_num="+"'"+phone+"')", null);
		}
		
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			bean=new DoctorBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
					cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getInt(8), cursor.getString(9), cursor.getString(10),
					cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getString(14), cursor.getString(15), cursor.getString(16),
					cursor.getString(17), cursor.getString(18), cursor.getString(19), cursor.getInt(20), cursor.getString(21));
		}
		if (cursor != null) {
			cursor.close();
		}
		return bean;
	}
	
	
	/**
	 * 根据邮箱查找手机号
	 * @param emails
	 * @return
	 */
	public List<String> getMobileByEmail(String[] emails){
		List<String> mobiles = new ArrayList<String>();
		StringBuilder builder=new StringBuilder();
		builder.append("(");
		if (emails.length>=2) {
			for (int i=0;i<emails.length-1;i++) {
				String email=emails[i];
				if (email!=null && !"".equals(email.trim())) {
					builder.append("'"+email+"',");
				}
			}
			String lastEmail=emails[emails.length-1];
			if (lastEmail!=null && !"".equals(lastEmail.trim())) {
				builder.append("'"+lastEmail+"'");
			}
		} else {
			builder.append("'"+emails[0]+"'");
		}		
		builder.append(")");
		
		Cursor cursor = null;
		String province = null, city = null, county = null;
		String[] region = selectUserRegion();
		if (region != null) {
			province = region[0] != null && region[0].trim().length() > 0 ? region[0] : null;
			city = region[1] != null && region[1].trim().length() > 0 ? region[1] : null;
			county = region[2] != null && region[2].trim().length() > 0 ? region[2] : null;
		}
		
		if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0 && county != null && county.trim().length() > 0) {
			cursor=db.rawQuery("select _mobile from "+ConstConfig.DOCTOR_TABLE+" where ((_cid<>-1 and _province=? and _city=? and _county=?) or _cid=-1) and (_email in"+builder.toString() + ")",
					new String[]{province, city, county});
		} else {
			cursor=db.rawQuery("select _mobile from "+ConstConfig.DOCTOR_TABLE+" where _cid=-1 and (_email in"+builder.toString() + ")", null);
		}
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				mobiles.add(cursor.getString(0));
			} while (cursor.moveToNext());			
		}
		cursor.close();
		return mobiles;
	}
	
	
	/**
	 * 获取用户信息
	 */
	public UserBean selectUserInfo(){
		UserBean bean=null;
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.USER_TABLE, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			try {
				String decryptedId = DES.decryptDES(cursor.getString(0), DES.KEY);
				String decryptedUsername=DES.decryptDES(cursor.getString(1), DES.KEY);
				String decryptedMm=DES.decryptDES(cursor.getString(2), DES.KEY);
				String decryptedSerial=DES.decryptDES(cursor.getString(15), DES.KEY);
				bean=new UserBean(Integer.valueOf(decryptedId), decryptedUsername, decryptedMm, cursor.getString(3), cursor.getString(4),
						cursor.getString(5), cursor.getString(6), cursor.getInt(7), cursor.getInt(8), cursor.getString(9), cursor.getString(10),
						cursor.getString(11), cursor.getString(12), cursor.getString(13), cursor.getString(14), decryptedSerial);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
		if (cursor != null) {
			cursor.close();
		}
		return bean;
	}
	
	/**
	 * 获取医师档案里的医师联系方式
	 * @param type 1手机2邮箱
	 * @return
	 */
	public List<ContactsBean> selectDoctorContacts(int type){
		List<ContactsBean> beanList=null;
		
		String province = null, city = null, county = null;
		String[] region = selectUserRegion();
		if (region != null) {
			province = region[0] != null && region[0].trim().length() > 0 ? region[0] : null;
			city = region[1] != null && region[1].trim().length() > 0 ? region[1] : null;
			county = region[2] != null && region[2].trim().length() > 0 ? region[2] : null;
		}
		
		if (type==1) {
			Cursor cursor = null;
			if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0 && county != null && county.trim().length() > 0) {
				cursor=db.rawQuery("select _id,_name,_mobile from "+ConstConfig.DOCTOR_TABLE+" where ((_cid<>-1 and _province=? and _city=? and _county=?) or _cid=-1) and _mobile is not null and _mobile<>''", new String[]{province, city, county});
			} else {
				cursor=db.rawQuery("select _id,_name,_mobile from "+ConstConfig.DOCTOR_TABLE+" where _cid=-1 and _mobile is not null and _mobile<>''", null);
			}
			
			if (cursor!=null && cursor.getCount()>0) {
				cursor.moveToFirst();
				beanList=new ArrayList<ContactsBean>();
				do {
					ContactsBean bean=new ContactsBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2));
					beanList.add(bean);
				} while (cursor.moveToNext());
			}
			if (cursor != null) {
				cursor.close();
			}
			
		} else if (type==2) {
			Cursor cursor = null;
			if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0 && county != null && county.trim().length() > 0) {
				cursor=db.rawQuery("select _id,_name,_email from "+ConstConfig.DOCTOR_TABLE+" where ((_cid<>-1 and _province=? and _city=? and _county=?) or _cid=-1) and _email is not null and _email<>''", new String[]{province, city, county});
			} else {
				cursor=db.rawQuery("select _id,_name,_email from "+ConstConfig.DOCTOR_TABLE+" where _cid=-1 and _email is not null and _email<>''", null);
			}
			if (cursor!=null && cursor.getCount()>0) {
				cursor.moveToFirst();
				beanList=new ArrayList<ContactsBean>();
				do {
					ContactsBean bean=new ContactsBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2));
					beanList.add(bean);
				} while (cursor.moveToNext());
			}
			if (cursor != null) {
				cursor.close();
			}
		}
		return beanList;
	}
	
	
	/**
	 * 查找用户的省市县
	 * @return
	 */
	public String[] selectUserRegion() {
		String[] region = null;
		Cursor cursor = db.rawQuery("select _province,_city,_county from " + ConstConfig.USER_REGION_TABLE, null);
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			region = new String[3];
			region[0] = cursor.getString(0);
			region[1] = cursor.getString(1);
			region[2] = cursor.getString(2);
		}
		if (cursor != null) {
			cursor.close();
		}
		return region;
	}
	
	
	/**
	 * 获取本地个人信息
	 * @return
	 */
	public List<String> selectUserLocalInfo(){
		List<String> localnfo = new ArrayList<String>(7);
		Cursor cursor = db.rawQuery("select _localName,_localPartment,_localHospital,_localPhoto from "+ ConstConfig.USER_TABLE, null);
		if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
			localnfo.add(cursor.getString(0));
			localnfo.add(cursor.getString(1));
			localnfo.add(cursor.getString(2));
			localnfo.add(cursor.getString(3));
		}
		if (cursor != null) {
			cursor.close();
		}
		return localnfo;
	}
	
	/**
	 * 获取登录页面的个人头像路径、帐号、密码
	 * @return
	 */
	public List<String> selectLoginInfo(){
		List<String> loginInfo = new ArrayList<String>();
		Cursor cursor=db.rawQuery("select _localPhoto,_username,_mm from "+ConstConfig.USER_TABLE, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			String account = cursor.getString(1);
			String password = cursor.getString(2);
			try {
				account=DES.decryptDES(account, DES.KEY);
				password=DES.decryptDES(password, DES.KEY);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			loginInfo.add(cursor.getString(0));
			loginInfo.add(account);
			loginInfo.add(password);
		}
		cursor.close();
		return loginInfo;
	}
	
	
	/**
	 * 根据id查找另一个文本字段
	 * @param table
	 * @param id
	 * @param field
	 * @return
	 */
	public String getFieldById(String table,int id,String field){
		String value=null;
		Cursor cursor=db.rawQuery("select "+field+" from "+table+" where _id="+id, null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			value=cursor.getString(0);
		}
		cursor.close();
		return value;
	}
	
	
	/**
	 * 根据搜索词查找疾病名称
	 * @param searchWord
	 * @return
	 */
	public List<String> selectDiseaseName(String searchWord){
		List<String> names=new ArrayList<String>();
		Cursor cursor = null;
		
		if (searchWord==null || "".equals(searchWord.trim())) {
			cursor=db.rawQuery("select _disease from "+ConstConfig.HEALTH_MANAGER_TABLE, null);	
		} else {
			cursor=db.rawQuery("select _disease from "+ConstConfig.HEALTH_MANAGER_TABLE+" where _disease like "+"'%"+searchWord+"%'", null);
		}
		
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				names.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		if (cursor!=null) {
			cursor.close();
		}
		return names;
	}
	
	
	/**
	 * 根据疾病名查找健康分析等3个字段
	 * @param disease
	 * @return
	 */
	public String[] getHealths(String disease){
		String[] healths=new String[3];
		Cursor cursor=db.rawQuery("select _health_ana,_health_instruct,_health_direct from "+ConstConfig.HEALTH_MANAGER_TABLE
				+" where _disease="+"'"+disease+"'", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			String decryptAna = null;
			String decryptInstruct = null;
			try {
				decryptAna=DES.decryptDES(cursor.getString(0), DES.KEY);
				decryptInstruct=DES.decryptDES(cursor.getString(1), DES.KEY);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			healths[0]=decryptAna;
			healths[1]=decryptInstruct;
			healths[2]=cursor.getString(2);
		}
		if (cursor != null) {
			cursor.close();
		}
		return healths;
	}
	
	
	/**
	 * 专长升级
	 * @param handler
	 */
	public void updateAdvantage(final Handler handler){
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int count=0;
				try {
					String url = URLConfig.ADVANTAGE_URL;
					HttpGet get = new HttpGet(url);
					DefaultHttpClient client = new DefaultHttpClient();
					HttpResponse response = client.execute(get);
					Message msg=handler.obtainMessage();
					if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						String result = EntityUtils.toString(response.getEntity());
						if (result==null) {
							LoadingTip.hideDialog();
							msg.what=5;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
							LoadingTip.hideDialog();
							msg.what=5;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						Gson gson=new Gson();
						AdvantageListBean list=gson.fromJson(result, AdvantageListBean.class);
						if (list==null || list.getSize()<=0) {
							LoadingTip.hideDialog();
							msg.what=5;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						//把解析得到的数据利用事务写入数据库
						db.beginTransaction();
						ContentValues values=new ContentValues();
						//先全部数据删除，再执行插入操作
						db.delete(ConstConfig.ADVANTAGE_TABLE, null, null);
						for (AdvantageBean bean : list.getDataList()) {
							values.clear();
							values.put("_id", bean.getId());
							values.put("_name", bean.getZcname());
							values.put("_order", bean.getZcnum());
							db.insert(ConstConfig.ADVANTAGE_TABLE, null, values);
							count++;
						}
						db.setTransactionSuccessful();
						db.endTransaction();
						LoadingTip.hideDialog();
						msg.what=5;
						msg.arg1=ConstConfig.SUCCESS;
						msg.arg2=count;
						handler.sendMessage(msg);
					} else {
						LoadingTip.hideDialog();
						msg.what=5;
						msg.arg1=ConstConfig.FAILED;
						msg.arg2=count;
						handler.sendMessage(msg);
						return;
					}
					
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}.start();
	}
	
	
	/**
	 * 升级了医学文章或视频后更新后台的时间
	 * @param account
	 * @param pwd
	 * @param action 医学文章或视频
	 */
	private void updateTime(String account, String pwd, String action) {
		HttpPost post = new HttpPost(URLConfig.UPDATE_TIME_URL + "?account=" + account + "&pwd=" + pwd
				+ "&action=" + action + "&serial_number=" + FileHelper.getImieStatus(MyApplication.getInstance()));
		DefaultHttpClient client = new DefaultHttpClient();
		final int MAX_LOOP = 3;  //最多尝试3次
		int i = 0;
		while (i < MAX_LOOP) {
			try {
				HttpResponse response = client.execute(post);
				if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
					byte[] data = EntityUtils.toByteArray(response.getEntity());
					if (data != null && data.length > 0) {
						String result = new String(data, "UTF-8");
						if (result != null) {
							result = result.trim();
							if ("1".equals(result)) {
								break;  //成功一次即退出循环
							}
						}
					}
				}
			} catch (ClientProtocolException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				i += 1;
			}
		}
	}
	
	
	/**
	 * 下载医学文章和文章图片(不包括视频的文章)
	 * @param handler
	 * @param msgWhat
	 * @param account
	 * @param pwd
	 */
	public void updateAllArticles(final Handler handler, final int msgWhat, final String account, final String pwd) {
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				List<Integer> notInsertIds = null;
				//现有最近下载的100篇医学知识文章
				Cursor cursor=db.rawQuery("select _id from "+ConstConfig.ARTICLE_TABLE+" where _dir not in (54,55) order by _id desc limit 100", null);
				if (cursor!=null && cursor.getCount()>0 && cursor.moveToFirst()) {
					notInsertIds=new ArrayList<Integer>();
					do {
						notInsertIds.add(cursor.getInt(0));
					} while (cursor.moveToNext());
				}
				cursor.close();
				
				//已被删除的医学知识文章
				Cursor deletedCursor=db.rawQuery("select _id from "+ConstConfig.DELETED_ARTICLE_TABLE+" where _dir not in (54,55)", null);
				if (deletedCursor!=null && deletedCursor.getCount()>0 && deletedCursor.moveToFirst()) {
					if (notInsertIds==null) {
						notInsertIds=new ArrayList<Integer>();
					}
					do {
						notInsertIds.add(deletedCursor.getInt(0));
					} while (deletedCursor.moveToNext());
				}
				deletedCursor.close();
				
				int page=1;
				Cursor pageCursor=db.rawQuery("select _articlePage from "+ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
				if (pageCursor!=null && pageCursor.moveToFirst()) {
					page=pageCursor.getInt(0);
				}
				pageCursor.close();
				
				int latestArticleId = 0;  //最近更新的文章id
				Cursor latestUpdateCousor = db.rawQuery("select _articleId from " + ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
				if (latestUpdateCousor != null) {
					latestUpdateCousor.moveToFirst();
					latestArticleId = latestUpdateCousor.getInt(0);
					latestUpdateCousor.close();
				}
				
				//开始下载医学文章
				ContentValues values=new ContentValues();
				int count=0;
				HashSet<Integer> updatedDirs = new HashSet<Integer>();  //本次升级中更新过的栏目
				boolean hasUpdateTime = false;
				outLoop:
				while (page<Integer.MAX_VALUE) {
					try {
						String url = URLConfig.KNOWLEDGE_UPDATE_URL+page;						
						HttpGet get = new HttpGet(url);
						DefaultHttpClient client = new DefaultHttpClient();
						
						HttpResponse response = client.execute(get);
						Message msg=handler.obtainMessage();
						
						if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
							HttpEntity entity= response.getEntity();
							String result = EntityUtils.toString(entity);
							
							if (result==null) {
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							result=result.trim();
							if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
								//全部下载完成
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							Gson gson=new Gson();
							ArticleListBean list=gson.fromJson(result, ArticleListBean.class);
							if (list==null || list.getSize()<=0) {
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							if (!hasUpdateTime) {
								updateTime(account, pwd, "article");
								hasUpdateTime = true;
							}
							
							//采用事务往数据库表里批量插入医学文章
							db.beginTransaction();
							int pageArticlesCount=0;
							for (ArticleBean bean : list.getDataList()) {
								if (notInsertIds!=null && notInsertIds.contains(bean.getId())) {
									//现有或已删除的文章不插入
									continue;
								}
								if (latestArticleId > 0 && bean.getId() <= latestArticleId) {
									//文章id<=最新更新的id说明这篇文章已经下载过了
									continue;
								}
								String encryptJj=null;
								String encryptContent = null;
								String jj=bean.getJj();
								String content=bean.getContent();
								try {
									encryptJj=DES.encryptDES(jj, DES.KEY);
									encryptContent=DES.encryptDES(content, DES.KEY);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								//普通文章从content提取图片url依次下载图片
								boolean downLoadImgResult = downImg(content);
								if (!downLoadImgResult) {
									//下载图片失败
									break outLoop;
								}
								//万一有重复则忽略插入
								db.execSQL("insert or ignore into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
										bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
										bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+","+1+");");
								
								values.clear();
								values.put("_articleId", bean.getId());
								db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, null, null);
								count++;
								pageArticlesCount++;
								if (!updatedDirs.contains(bean.getDir())) {
									HashSet<Integer> needUpdateDirs = needrefreshDirsNew(bean.getDir());
									if (needUpdateDirs != null && !needUpdateDirs.isEmpty()) {
										StringBuilder builder = new StringBuilder();
										builder.append("(");
										for (int id : needUpdateDirs) {
											builder.append(id);
											builder.append(",");
										}
										builder.replace(builder.length() - 1, builder.length(), ")");
										values.clear();
										values.put("_is_new", 1);
										int newCount = db.update(ConstConfig.CATEGORT_TABLE, values, "_id in " + builder.toString(), null);
										if (newCount > 0) {
											updatedDirs.addAll(needUpdateDirs);
										}
									}
								}
								Message message = handler.obtainMessage();
								message.what = BaseFragmentActivity.MSG_UPFATE_COUNT;
								message.arg1 = count;
								handler.sendMessage(message);
							}
							//更新数据升级记录表
							values.clear();
							values.put("_articlePage", page);
							db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, null, null);
							page++;
							if (pageArticlesCount>0) {
								//下载完一个page后更新文章数量
								updateUpdateRecord("_articleCount", pageArticlesCount);
							}
							db.setTransactionSuccessful();
							db.endTransaction();
							
						} else {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.FAILED;
							msg.arg2=count;
							handler.sendMessage(msg);
							break;
						}
						
					} catch (ClientProtocolException e) {
						e.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}.start();
	}
	
	
	/**
	 * 获取需要更新为new的栏目
	 * @param dirId 子栏目id
	 * @return
	 */
	private HashSet<Integer> needrefreshDirsNew(int dirId){
//		
//		Cursor cursor = db.rawQuery("with recursive tem(_id,_parent_id,_is_new) as (select _id,_parent_id,_is_new from ? where _id=?"
//				+ " union select a._id,a._parent_id,_is_new from ? a,tem b where b._parent_id=a._id) select _id from tem where _is_new<>1", 
//				new String[]{ConstConfig.CATEGORT_TABLE, String.valueOf(dirId), ConstConfig.CATEGORT_TABLE});
		
		int topParentId = dirId;
		HashSet<Integer> dirs = new HashSet<Integer>();
		dirs.add(dirId);
		while (topParentId > 0) {
			Cursor cursor = db.rawQuery("select _parent_id from " + ConstConfig.CATEGORT_TABLE + " where _id=?", new String[]{String.valueOf(topParentId)});
			if (cursor != null && cursor.moveToFirst()) {
				topParentId = cursor.getInt(0);
				dirs.add(topParentId);
				cursor.close();
			} else {
				topParentId = 0;
			}
		}
		
		
//		if (cursor != null) {
//			if (cursor.moveToFirst()) {
//				dirs = new HashSet<Integer>(cursor.getCount());
//				do {
//					dirs.add(cursor.getInt(0));
//				} while (cursor.moveToNext());
//			}
//			cursor.close();
//		}
		return dirs;
	}
	
	
	/**
	 * 下载视频文件和视频文章
	 * @param handler
	 * @param msgWhat
	 * @param account
	 * @param pwd
	 */
	public void updateAllVideos(final Handler handler, final int msgWhat, final String account, final String pwd){
		new Thread(){
			@Override
			public void run() {
				List<Integer> notInsertIds = null;
				//现有最近下载的100篇
				Cursor cursor=db.rawQuery("select _id from "+ConstConfig.ARTICLE_TABLE+" where _dir in (54,55) order by _id desc limit 100", null);
				if (cursor!=null && cursor.getCount()>0 && cursor.moveToFirst()) {
					notInsertIds=new ArrayList<Integer>();
					do {
						notInsertIds.add(cursor.getInt(0));
					} while (cursor.moveToNext());
				}
				cursor.close();
				
				//已被删除的
				Cursor deletedCursor=db.rawQuery("select _id from "+ConstConfig.DELETED_ARTICLE_TABLE+" where _dir in (54,55)", null);
				if (deletedCursor!=null && deletedCursor.getCount()>0 && deletedCursor.moveToFirst()) {
					if (notInsertIds==null) {
						notInsertIds=new ArrayList<Integer>();
					}
					do {
						notInsertIds.add(deletedCursor.getInt(0));
					} while (deletedCursor.moveToNext());
				}
				
				int page=1;
				Cursor pageCursor=db.rawQuery("select _videoPage from "+ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
				if (pageCursor!=null && pageCursor.moveToFirst()) {
					page=pageCursor.getInt(0);
				}
				pageCursor.close();
				
				int latestVideoId = 0;  //最近更新的视频id
				Cursor latestUpdateCousor = db.rawQuery("select _videoId from " + ConstConfig.DATA_UPDATE_RECORD_TABLE, null);
				if (latestUpdateCousor != null) {
					latestUpdateCousor.moveToFirst();
					latestVideoId = latestUpdateCousor.getInt(0);
					latestUpdateCousor.close();
				}
				
				//开始下载视频文章
				ContentValues values=new ContentValues();
				int count=0;
				HashSet<Integer> updatedDirs = new HashSet<Integer>();  //本次升级中更新过的栏目
				boolean hasUpdateTime = false;
				outLoop:
				while (page<Integer.MAX_VALUE) {
					try {
						String url = URLConfig.VIDEO_UPDATE_URL+page;						
						HttpGet get = new HttpGet(url);
						DefaultHttpClient client = new DefaultHttpClient();
						
						HttpResponse response = client.execute(get);
						Message msg=handler.obtainMessage();
						
						if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
							HttpEntity entity= response.getEntity();
							String result = EntityUtils.toString(entity);
							
							if (result==null) {
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							result=result.trim();
							if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
								//全部下载完成
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							Gson gson=new Gson();
							ArticleListBean list=gson.fromJson(result, ArticleListBean.class);
							if (list==null || list.getSize()<=0) {
								msg.what=msgWhat;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							
							if (!hasUpdateTime) {
								updateTime(account, pwd, "video");
								hasUpdateTime = true;
							}
							
							for (ArticleBean bean : list.getDataList()) {
								if (notInsertIds!=null && notInsertIds.contains(bean.getId())) {
									//现有或已删除的视频文章不插入
									continue;
								}
								if (latestVideoId > 0 && bean.getId() <= latestVideoId) {
									//视频文章id<=最新更新的id说明这篇文章已经下载过了
									continue;
								}
								/**
								 * 采用事务往数据库表里批量插入医学文章
								 * 下载完一个视频文件后就更新相应的数据库信息
								 * 这点与医学知识库不同
								 */
								db.beginTransaction();
								String encryptJj=null;
								String encryptContent = null;
								String jj=bean.getJj();
								String content=bean.getContent();
								try {
									encryptJj=DES.encryptDES(jj, DES.KEY);
									encryptContent=DES.encryptDES(content, DES.KEY);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								//从简介中获取视频url来下载视频文件
								boolean downVideoResult = downVideo(jj);
								if (!downVideoResult) {
									//视频文件下载失败则不予存入数据库中
									break outLoop;
								}
								
								//下载图片
								boolean downImgResult = downImg(content);
								if (!downImgResult) {
									break outLoop;
								}
								
								
								//万一有重复则忽略插入
								db.execSQL("insert or ignore into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
										bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
										bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+","+1+");");
								
								values.clear();
								values.put("_videoId", bean.getId());
								db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, null, null);
								count++;
								
								if (!updatedDirs.contains(bean.getDir())) {
									HashSet<Integer> needUpdateDirs = needrefreshDirsNew(bean.getDir());
									if (needUpdateDirs != null && !needUpdateDirs.isEmpty()) {
										StringBuilder builder = new StringBuilder();
										builder.append("(");
										for (int id : needUpdateDirs) {
											builder.append(id);
											builder.append(",");
										}
										builder.replace(builder.length() - 1, builder.length(), ")");
										values.clear();
										values.put("_is_new", 1);
										int newCount = db.update(ConstConfig.CATEGORT_TABLE, values, "_id in " + builder.toString(), null);
										if (newCount > 0) {
											updatedDirs.addAll(needUpdateDirs);
										}
									}
								}
								
								Message message = handler.obtainMessage();
								message.what = BaseFragmentActivity.MSG_UPFATE_COUNT;
								message.arg1 = count;
								handler.sendMessage(message);
								//下载完一个视频后更新视频数量
								updateUpdateRecord("_videoCount", 1);
								db.setTransactionSuccessful();
								db.endTransaction();
							}
							//更新数据升级记录表
							values.clear();
							values.put("_videoPage", page);
							db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, null, null);
							page++;
							
							
						} else {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.FAILED;
							msg.arg2=count;
							handler.sendMessage(msg);
							break;
						}
					} catch (ClientProtocolException e) {
						e.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}.start();
	}
	
	
	
	
	
	
	/**
	 * 根据父栏目id下载存储医学文章
	 * @param parentId
	 */
	public void updateArticles(final int parentId, final Handler handler){
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				//父栏目下的所有叶子节点栏目id
				List<Integer> dirIds=new ArrayList<Integer>();
				List<Integer> notInsertIds=new ArrayList<Integer>();
				//四级目录
				Cursor cursor4=db.rawQuery("select _id from "+ConstConfig.CATEGORT_TABLE+" where _parent_id in (select _id from "+ConstConfig.CATEGORT_TABLE+
						" where _parent_id in (select _id from "+ConstConfig.CATEGORT_TABLE+" where _parent_id="+parentId+"))", null);
				if (cursor4!=null && cursor4.getCount()>0) {
					cursor4.moveToFirst();
					do {
						dirIds.add(cursor4.getInt(0));
					} while (cursor4.moveToNext());
				} 
				cursor4.close();
				
				//三级目录
				Cursor cursor3=db.rawQuery("select _id from "+ConstConfig.CATEGORT_TABLE+" where _parent_id in (select _id from "+ConstConfig.CATEGORT_TABLE+
							" where _parent_id="+parentId+")", null);
				if (cursor3!=null && cursor3.getCount()>0) {
					cursor3.moveToFirst();
					do {
						dirIds.add(cursor3.getInt(0));
					} while (cursor3.moveToNext());
				}
				cursor3.close();
					
				//二级目录
				Cursor cursor2=db.rawQuery("select _id from "+ConstConfig.CATEGORT_TABLE+" where _parent_id="+parentId, null);
				if (cursor2!=null && cursor2.getCount()>0) {
					cursor2.moveToFirst();
					do {
						dirIds.add(cursor2.getInt(0));
					} while (cursor2.moveToNext());
				}
				cursor2.close();
				
				StringBuilder builder=new StringBuilder();
				builder.append("(");
				if (dirIds.size()>0) {
					for (int id : dirIds) {
						builder.append(id+",");
					}
				}
				builder.append(parentId+")");
				String ids=builder.toString();
				
				//根据目录id从现有表和已删除的表中查找文章id，这些文章id是不需要插入的
				Cursor cursor= db.rawQuery("select _id from "+ConstConfig.ARTICLE_TABLE+" where _dir in"+ids+" union select _id from "+
				    ConstConfig.DELETED_ARTICLE_TABLE+" where _dir in"+ids, null);
				if (cursor!=null && cursor.getCount()>0) {
					cursor.moveToFirst();
					do {
						notInsertIds.add(cursor.getInt(0));
					} while (cursor.moveToNext());
				}
				cursor.close();
				
				int page=1;
				Cursor pageCursor=db.rawQuery("select _page from "+ConstConfig.DATA_UPDATE_RECORD_TABLE+" where _dir="+parentId+
						" and _table="+"'"+ConstConfig.ARTICLE_TABLE+"'", null);
				if (pageCursor!=null && pageCursor.getCount()>0) {
					pageCursor.moveToFirst();
					page=pageCursor.getInt(0);
				} else {
					//没有升级记录则插入page=1的数据
					db.execSQL("insert into "+ConstConfig.DATA_UPDATE_RECORD_TABLE+" values("+parentId+","+1+","+"'"+ConstConfig.ARTICLE_TABLE+"'"+");");
				}
				pageCursor.close();
				
				//开始下载医学文章
				ContentValues values=new ContentValues();
				int count=0;
				outLoop:
				while (page<Integer.MAX_VALUE) {
					try {
						String url = URLConfig.ARTICLE_UPDATE_URL+parentId+"&page="+page;						
						HttpGet get = new HttpGet(url);
						DefaultHttpClient client = new DefaultHttpClient();
						
						HttpResponse response = client.execute(get);
						Message msg=handler.obtainMessage();
						if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
							HttpEntity entity= response.getEntity();
							String result = EntityUtils.toString(entity);
							if (result==null) {
								LoadingTip.hideDialog();
								msg.what=3;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							result=result.trim();
							if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
								LoadingTip.hideDialog();
								msg.what=3;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							Gson gson=new Gson();
							ArticleListBean list=gson.fromJson(result, ArticleListBean.class);
							if (list==null || list.getSize()<=0) {
								LoadingTip.hideDialog();
								msg.what=3;
								msg.arg1=ConstConfig.SUCCESS;
								msg.arg2=count;
								handler.sendMessage(msg);
								break;
							}
							//采用事务往数据库表里批量插入医学文章
							db.beginTransaction();
							for (ArticleBean bean : list.getDataList()) {
								if (notInsertIds.contains(bean.getId())) {
									//现有或已删除的文章不插入
									continue;
								}
								String encryptJj=null;
								String encryptContent = null;
								String jj=bean.getJj();
								String content=bean.getContent();
								try {
									encryptJj=DES.encryptDES(jj, DES.KEY);
									encryptContent=DES.encryptDES(content, DES.KEY);
								} catch (Exception e) {
									// TODO Auto-generated catch block
									e.printStackTrace();
								}
								if (parentId==51) {
									//下载视频
									boolean downVideoResult = downVideo(jj);
									if (!downVideoResult) {
										//视频文件下载失败则不予存入数据库中
										break outLoop;
									}
								} else {
									//普通文章从content提取图片url依次下载图片
									boolean downImgResult = downImg(content);
									if (!downImgResult) {
										break outLoop;
									}
								}
								//万一有重复则忽略插入
								db.execSQL("insert or ignore into "+ConstConfig.ARTICLE_TABLE+" values("+bean.getId()+","+"'"+bean.getTitle()+"'"+","+"'"+encryptJj+"'"+","+
										bean.getDir()+","+"'"+encryptContent+"'"+","+"'"+bean.getPic()+"'"+","+bean.getHits()+","+"'"+bean.getAuthor()+"'"+","+bean.getState()+","+"'"+
										bean.getAddtime()+"'"+","+bean.getOrderid()+","+bean.getIs_free()+");");
								
								count++;
							}
							db.setTransactionSuccessful();
							db.endTransaction();
							//更新数据升级记录表
							values.clear();
							values.put("_page", page);
							db.update(ConstConfig.DATA_UPDATE_RECORD_TABLE, values, "_dir=? and _table=?", new String[]{String.valueOf(parentId),ConstConfig.ARTICLE_TABLE});
							page++;
							
						} else {
							LoadingTip.hideDialog();
							msg.what=3;
							msg.arg1=ConstConfig.FAILED;
							msg.arg2=count;
							handler.sendMessage(msg);
							break;
						}
					} catch (ClientProtocolException e) {
						e.printStackTrace();
					} catch (IOException e) {
						e.printStackTrace();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			}
		}.start();	
	}
	
	
	
	/**
	 * 验证用户是否在有效期内
	 * @param handler
	 * @param msgWhat
	 * @param account
	 * @param pwd
	 */
	public void verifyUserDate(final Handler handler ,final int msgWhat, final String account, final String pwd){
		new Thread(){
			public void run() {
				try {
					HttpPost postVerify = new HttpPost(URLConfig.VERIFY_USER_VALID_DATE_URL);
					DefaultHttpClient clientVerify = new DefaultHttpClient();
					List<BasicNameValuePair> params=new ArrayList<BasicNameValuePair>();
					BasicNameValuePair paramAccount=new BasicNameValuePair("account", account);
					BasicNameValuePair paramPwd=new BasicNameValuePair("pwd", pwd);
					BasicNameValuePair paramSerial=new BasicNameValuePair("serial_number", FileHelper.getImieStatus(MyApplication.getInstance()));
					params.add(paramAccount);
					params.add(paramPwd);
					params.add(paramSerial);
					HttpEntity entityVerify = new UrlEncodedFormEntity(params);
					postVerify.setEntity(entityVerify);
					HttpResponse responseVerify = clientVerify.execute(postVerify);
					Message msgVerify=handler.obtainMessage();
					String[] msgInfo = {null, account, pwd};  //发送验证结果消息，包括验证结果、帐号、密码
					msgVerify.what = msgWhat;
					msgVerify.obj = msgInfo;
					if (responseVerify.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						//判断是否已过有效期
						String resultVerify = EntityUtils.toString(responseVerify.getEntity(), "GBK");
						if (resultVerify==null || "".equals(resultVerify)) {
							msgInfo[0] = "出错啦！";
							handler.sendMessage(msgVerify);
							return;
						}
						resultVerify=resultVerify.trim();
						if ("不存在该账户".equals(resultVerify) || "平板序列号错误".equals(resultVerify)) {
							msgInfo[0] = "用户名或密码错误！";
							handler.sendMessage(msgVerify);
							return;
						} else if ("账号已过有效期！请充值或者联系客服".equals(resultVerify)) {
							msgInfo[0] = "帐号已过有效期！请充值！\n充值电话：医师宝客服中心 18702537275 18702613050";
							handler.sendMessage(msgVerify);
							return;
						} else if (ConstConfig.ACCOUNT_VALID.equals(resultVerify)) {
							msgInfo[0] = ConstConfig.ACCOUNT_VALID;
							handler.sendMessage(msgVerify);
							return;
						} else if ("账号未激活请注册".equals(resultVerify)) {
							msgInfo[0] = "账号未激活请先注册！";
							handler.sendMessage(msgVerify);
							return;
						} else if (!ConstConfig.ACCOUNT_VALID.equals(resultVerify)) {
							msgInfo[0] = "出错啦！";
							handler.sendMessage(msgVerify);
							return;
						}
					} else {
						msgInfo[0] = "出错啦！";
						handler.sendMessage(msgVerify);
						return;
					}
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
			};
		}.start();
	}
	
	
	
	/**
	 * 下载存储医学知识栏目
	 */
	public void updateDirs(final Handler handler, final int msgWhat){
		new Thread(){
			public void run() {
				int count=0;
				try {
					List<Integer> notInsertIds=new ArrayList<Integer>(); //现有的目录id
					Cursor cursor= db.rawQuery("select _id from "+ConstConfig.CATEGORT_TABLE, null);
					if (cursor!=null && cursor.getCount()>0) {
						cursor.moveToFirst();
						do {
							notInsertIds.add(cursor.getInt(0));
						} while (cursor.moveToNext());
					}
					
					HttpGet get = new HttpGet(URLConfig.DIR_URL);
					DefaultHttpClient client = new DefaultHttpClient();
					HttpResponse response = client.execute(get);
					Message msg=handler.obtainMessage();
					if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						String result = EntityUtils.toString(response.getEntity());
						if (result==null) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						Gson gson=new Gson();
						KnowledgeListBean list=gson.fromJson(result, KnowledgeListBean.class);
						if (list==null || list.getSize()<=0) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						
						//把解析得到的数据利用事务写入数据库
						db.beginTransaction();
						ContentValues values=new ContentValues();
						for (KnowledgeBean bean : list.getDataList()) {
							if (notInsertIds.contains(bean.getId())) {
								continue;
							}
							values.clear();
							values.put("_id", bean.getId());
							values.put("_parent_id", bean.getPid());
							values.put("_name", bean.getDirname());
							db.insert(ConstConfig.CATEGORT_TABLE, null, values);
							count++;
						}
						db.setTransactionSuccessful();
						db.endTransaction();
						msg.what=msgWhat;
						msg.arg1=ConstConfig.SUCCESS;
						msg.arg2=count;
						handler.sendMessage(msg);
					} else {
						msg.what=msgWhat;
						msg.arg1=ConstConfig.FAILED;
						msg.arg2=count;
						handler.sendMessage(msg);
						return;
					}
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
			};
		}.start();
	}
	
	

	
	
	/**
	 * 专家群升级
	 */
	public void updateDoctors(final Handler handler){
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int count=0;
				try {
					List<Integer> notInsertIds=new ArrayList<Integer>(); //现有和已删除的专家群id
					Cursor cursor= db.rawQuery("select _cid from "+ConstConfig.DOCTOR_TABLE+" where _cid>=0 union select distinct _cid from "+ConstConfig.DELETED_DOCTOR_TABLE, null);
					if (cursor!=null && cursor.getCount()>0) {
						cursor.moveToFirst();
						do {
							notInsertIds.add(cursor.getInt(0));
						} while (cursor.moveToNext());
					}
					cursor.close();
					
					HttpGet get = new HttpGet(URLConfig.DOCTOR_INFO_URL);
					DefaultHttpClient client = new DefaultHttpClient();
					HttpResponse response = client.execute(get);
					Message msg=handler.obtainMessage();
					if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						String result = EntityUtils.toString(response.getEntity());
						if (result==null) {
							LoadingTip.hideDialog();
							msg.what=1;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
							LoadingTip.hideDialog();
							msg.what=1;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						Gson gson=new Gson();
						DoctorListBean list=gson.fromJson(result, DoctorListBean.class);
						if (list==null || list.getSize()<=0) {
							LoadingTip.hideDialog();
							msg.what=1;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						//把解析得到的数据利用事务写入数据库
						db.beginTransaction();
						ContentValues values=new ContentValues();
						for (DoctorBean bean : list.getDataList()) {
							if (notInsertIds.contains(bean.getId())) {
								continue;
							}
							values.clear();
							values.put("_cid", bean.getId());
							values.put("_name", bean.getRealname());
							values.put("_sex", bean.getSex());
							values.put("_hospital", bean.getHospital());
							values.put("_birth_day", bean.getBrith());
							values.put("_post_title", bean.getZc());
							values.put("_partment", bean.getKs());
							values.put("_post", bean.getZw());
							values.put("_advantage", bean.getExpertise());
							values.put("_time", bean.getCztime());
							values.put("_address", bean.getAddress());
							values.put("_phone", bean.getTel());
							values.put("_mobile", bean.getMobile());
							values.put("_special_num", bean.getZh());
							values.put("_email", bean.getEmail());
							values.put("_qq", bean.getQq());
							values.put("_blog", bean.getBlog());
							values.put("_fetion", bean.getFx());
							values.put("_webchat", bean.getWx());
							values.put("_other", bean.getRemark());	
							values.put("_webSite", bean.getWebsite());
							db.insert(ConstConfig.DOCTOR_TABLE, null, values);
							count++;
						}
						db.setTransactionSuccessful();
						db.endTransaction();
						msg.what=1;
						msg.arg1=ConstConfig.SUCCESS;
						msg.arg2=count;
						handler.sendMessage(msg);
					} else {
						LoadingTip.hideDialog();
						msg.what=1;
						msg.arg1=ConstConfig.FAILED;
						msg.arg2=count;
						handler.sendMessage(msg);
						return;
					}
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
				LoadingTip.hideDialog();
			}
		}.start();
	}
	
	
	/**
	 * 健康管理升级
	 */
	public void updateHealthManager(final Handler handler, final int msgWhat){
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int count=0;
				try {
//					List<Integer> notInsertIds=new ArrayList<Integer>(); //现有的健康管理
//					Cursor cursor=db.rawQuery("select _id from "+ConstConfig.HEALTH_MANAGER_TABLE, null);
//					if (cursor!=null && cursor.getCount()>0) {
//						cursor.moveToFirst();
//						do {
//							notInsertIds.add(cursor.getInt(0));
//						} while (cursor.moveToNext());
//					}
//					cursor.close();
					
					HttpGet get = new HttpGet(URLConfig.HEALTH_MANAGER_URL);
					DefaultHttpClient client = new DefaultHttpClient();
					HttpResponse response = client.execute(get);
					Message msg=handler.obtainMessage();
					if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						String result = EntityUtils.toString(response.getEntity());
						if (result==null) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						Gson gson=new Gson();
						HealthManagerListBean list=gson.fromJson(result, HealthManagerListBean.class);
						if (list==null || list.getSize()<=0) {
							msg.what=msgWhat;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						//把解析得到的数据利用事务写入数据库
						db.beginTransaction();
						db.delete(ConstConfig.HEALTH_MANAGER_TABLE, null, null);
						ContentValues values=new ContentValues();
						for (HealthManagerBean bean : list.getDataList()) {
//							if (notInsertIds.contains(bean.getId())) {
//								continue;
//							}
							values.clear();
							String encryptAna = null;
							String encryptInstruct = null;
							try {
								encryptAna=DES.encryptDES(bean.getJkfx(), DES.KEY);
								encryptInstruct=DES.encryptDES(bean.getJkzd(), DES.KEY);
							} catch (Exception e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
							values.put("_id", bean.getId());
							values.put("_disease", bean.getJb());
							values.put("_health_ana", encryptAna);
							values.put("_health_instruct", encryptInstruct);
							values.put("_health_direct", bean.getJkts());
							db.insert(ConstConfig.HEALTH_MANAGER_TABLE, null, values);
							count++;
						}
						db.setTransactionSuccessful();
						db.endTransaction();
						msg.what=msgWhat;
						msg.arg1=ConstConfig.SUCCESS;
						msg.arg2=count;
						handler.sendMessage(msg);
					} else {
						msg.what=msgWhat;
						msg.arg1=ConstConfig.FAILED;
						msg.arg2=count;
						handler.sendMessage(msg);
						return;
					}
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}.start();
	}
	
	
	/**
	 * 获取自带的专家群档案数量
	 * @return
	 */
	public int getFreeDoctorCount(){
		int count=-1;
		Cursor cursor=db.rawQuery("select count(_cid) from "+ConstConfig.DOCTOR_TABLE+" where _cid>=0", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			count=cursor.getInt(cursor.getColumnIndex("count(_cid)"));
		}
		cursor.close();
		return count;
	}
	
	
	/**
	 * 搜索患者档案
	 * @param keyWord
	 * @return
	 */
	public PatientFileListBean searchPatient(String keyWord){
		List<PatientFileBean> beanList=new ArrayList<PatientFileBean>();
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.PATIENT_FILE_TABLE+" where _id='"+keyWord+"'"+
				" or _name like "+"'%"+keyWord+"%'"+" order by _id desc", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				PatientFileBean bean=new PatientFileBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
						cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getString(7), cursor.getString(8), cursor.getString(9),
						cursor.getString(10), cursor.getString(11), cursor.getString(12), cursor.getString(13));
				bean.setMarriage(cursor.getString(16));
				bean.setContacts(cursor.getString(17));
				bean.setContactsMobile(cursor.getString(18));
				bean.setAllergy(cursor.getString(19));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return new PatientFileListBean(beanList);
	}
	
	
	/**
	 * 获取全部专长数据
	 * @return
	 */
	public Map<Integer, String> selectAdvantage1(){
		Map<Integer, String> map=new HashMap<Integer, String>();
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.ADVANTAGE_TABLE+" order by _order", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				map.put(cursor.getInt(0), cursor.getString(1));
			} while (cursor.moveToNext());
		}
		cursor.close();
		return map;
	}
	
	
	/**
	 * 获取全部专长数据
	 * @return
	 */
	public Map<String, Integer> selectAdvantage2(){
		Map<String, Integer> map=new LinkedHashMap<String, Integer>();
		Cursor cursor=db.rawQuery("select * from "+ConstConfig.ADVANTAGE_TABLE+" order by _order", null);
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			do {
				map.put(cursor.getString(1), cursor.getInt(0));
			} while (cursor.moveToNext());
		}
		cursor.close();
		return map;
	}
	
	
	/**
	 * 切分url下载图片 
	 * @param content
	 * @return 是否下载完成
	 */
	private boolean downImg(String content){
		if (content == null || content.trim().length() <= 0) {
			return true;
		}
		String[] contentArray=content.split("<img");
		Map<File, String> map=new HashMap<File, String>();
		if (contentArray.length>=2) {
			for (int i = 1; i < contentArray.length; i++) {
				int srcIndex=contentArray[i].indexOf("src=\"");
				int endIndex=contentArray[i].indexOf("\"", srcIndex+"src=\"".length());
				
				String pathAndPic=contentArray[i].substring(srcIndex+"src=\"".length(),endIndex);
				int picIndex=pathAndPic.lastIndexOf("/");
				String path=pathAndPic.substring(pathAndPic.indexOf("/upload")+1,picIndex+1);
				String pic=pathAndPic.substring(picIndex+1, pathAndPic.length());
				
				File imgFile=new File(ConstConfig.ARTICLE_IMG_FILE+"/"+pic);
				if (!imgFile.exists()) {
					map.put(imgFile, URLConfig.WEBSITE+path+pic);
				}
			}				
		}
		
		if (map.size() > 0) {
			return downLoadImg(map);
		} else {
			return true;
		}
	}
	
	
	/**
	 * 下载图片
	 * @param map
	 * @return 是否下载完成
	 */
	private boolean downLoadImg(final Map<File, String> map){
		boolean result = true;
		for (File imgFile : map.keySet()) {
			if (!NetUtil.hasNetwork(MyApplication.getInstance())) {
				result = false;
				return result;
			}
			//依次下载图片
			FileOutputStream out = null;
			InputStream is = null;

			try {
				URL url=new URL(map.get(imgFile));
				HttpURLConnection httpConnection = (HttpURLConnection) url.openConnection();

				imgFile.createNewFile();
				out = new FileOutputStream(imgFile, true);

				is = httpConnection.getInputStream();

				int length = httpConnection.getContentLength();
				if (length <= 0) {
					result = false;
					return result;
				}

				byte[] buff = new byte[1024];
				int size = 0;
				int downLoadedLength = 0;  //已经下载了的内容长度

				while ((size = is.read(buff)) != -1) {
					try {
						out.write(buff, 0, size);
						downLoadedLength += size;
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				result = false;
				break;
			} finally {
				if (out != null) {
					try {
						out.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}

				if (is != null) {
					try {
						is.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		return result;
	}
	
	
	
	/**
	 * 根据简介切分视频url并下载视频文件保存在本地存储
	 * @param briefIntro
	 * @return 是否下载完成
	 */
	private boolean downVideo(String briefIntro){
		if (briefIntro == null || briefIntro.trim().length() <= 0) {
			return true;
		}
		
		int divide=briefIntro.lastIndexOf("/");
		String medium=briefIntro.substring(0, divide+1);
		String videoName=briefIntro.substring(divide+1);
		String[] videoPart=videoName.split("\\.");
		String enCodeName = null;
		try {
			//中文需进行utf-8编码
			enCodeName=URLEncoder.encode(videoPart[0], "UTF-8");
		} catch (UnsupportedEncodingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		
		File dir=new File(ConstConfig.VIDEO_FILE_INER);
		if (!dir.exists()) {
			boolean result= dir.mkdirs();
			if (!result) {
				return false;
			}
		}
		
		File file=new File(dir, videoName);
		if (file.exists()) {
			//已有视频文件则先将其删除，以免保留下载不完整的文件
			file.delete();
		}
		
		//下载视频文件
		String videoUrl=URLConfig.WEBSITE.substring(0, URLConfig.WEBSITE.length()-1)+medium+enCodeName+"."+videoPart[1];
		String cacheName=ConstConfig.VIDEO_FILE_INER + "/" + videoName;
		return FileHelper.downVideo(videoUrl, cacheName);
	}
	
	
	/**
	 * 预约服务
	 * @param fromRow
	 * @return
	 */
	public List<BookServiceBean> selectBookServiceData(int fromRow, String province, String city, String county){
		List<BookServiceBean> beanList=null;
		Cursor cursor = null;
		if (province != null && province.trim().length() > 0 && city != null && city.trim().length() > 0) {
			//本地添加的数据排在前面显示
			cursor=db.rawQuery("select *,0 as source from "+ConstConfig.HOSPITAL_TABLE+" where _cid is null or _cid<=0 union "
					+ "select *,1 as source from "+ConstConfig.HOSPITAL_TABLE+" where _cid>0 and _province=? and _city=? and _county=? order by source asc,_cid asc limit "
					+maxCount+" offset "+fromRow, new String[]{province, city, county});
		} else {
			//本地添加的数据排在前面显示
			cursor=db.rawQuery("select * from "+ConstConfig.HOSPITAL_TABLE+" where _cid is null or _cid<=0 limit "+maxCount+" offset "+fromRow, null);
		}
		
		if (cursor!=null && cursor.getCount()>0) {
			cursor.moveToFirst();
			beanList=new ArrayList<BookServiceBean>();
			do {
				BookServiceBean bean=new BookServiceBean(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3),
						cursor.getString(4), cursor.getInt(5));
				beanList.add(bean);
			} while (cursor.moveToNext());
		}
		cursor.close();
		return beanList;
	}
	
	
	
	/**
	 * 预约服务数据升级(从网络下载的)
	 */
	public void updateBookService(final Handler handler){
		new Thread(){
			@Override
			public void run() {
				// TODO Auto-generated method stub
				int count=0;
				try {
					//本地添加的数据不能被删除
					StringBuilder builder = null;
					Cursor filterIdsCursor=db.rawQuery("select _id from "+ConstConfig.HOSPITAL_TABLE+" where _cid is null or _cid<=0", null);
					if (filterIdsCursor!=null && filterIdsCursor.getCount()>0) {
						filterIdsCursor.moveToFirst();
						builder=new StringBuilder();
						builder.append("(");
						do {
							builder.append("'"+filterIdsCursor.getInt(0)+"'"+",");
						} while (filterIdsCursor.moveToNext());
						builder.replace(builder.length()-1, builder.length(), ")");
					}
					filterIdsCursor.close();
					
					HttpGet get = new HttpGet(URLConfig.BOOK_SERVICE_URL);
					DefaultHttpClient client = new DefaultHttpClient();
					HttpResponse response = client.execute(get);
					Message msg=handler.obtainMessage();
					if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
						String result = EntityUtils.toString(response.getEntity());
						if (result==null) {
							LoadingTip.hideDialog();
							msg.what=6;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						if (result.length()<=15 || !"[".equals(result.substring(12, 13))) {
							LoadingTip.hideDialog();
							msg.what=6;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						result=result.trim();
						Gson gson=new Gson();
						BookServiceListBean list=gson.fromJson(result, BookServiceListBean.class);
						if (list==null || list.getSize()<=0) {
							LoadingTip.hideDialog();
							msg.what=6;
							msg.arg1=ConstConfig.SUCCESS;
							msg.arg2=count;
							handler.sendMessage(msg);
							return;
						}
						
						db.beginTransaction();
						//先删除原有数据(本地添加的除外)，把解析得到的数据利用事务写入数据库
						if (builder!=null) {
							db.delete(ConstConfig.HOSPITAL_TABLE, "_id not in "+builder.toString(), null);
						} else {
							db.delete(ConstConfig.HOSPITAL_TABLE, null, null);
						}
						ContentValues values=new ContentValues();
						for (BookServiceBean bean : list.getDataList()) {
							values.clear();
							values.put("_cid", bean.getId());
							values.put("_hospital_partment", bean.getHospital());
							values.put("_phone", bean.getTel());
							values.put("_special_num", bean.getGh());
							values.put("_webSite", bean.getWebsite());
							db.insert(ConstConfig.HOSPITAL_TABLE, null, values);
							count++;
						}
						db.setTransactionSuccessful();
						db.endTransaction();
						msg.what=6;
						msg.arg1=ConstConfig.SUCCESS;
						msg.arg2=count;
						handler.sendMessage(msg);
					} else {
						LoadingTip.hideDialog();
						msg.what=6;
						msg.arg1=ConstConfig.FAILED;
						msg.arg2=count;
						handler.sendMessage(msg);
						return;
					}
				} catch (ClientProtocolException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}
				LoadingTip.hideDialog();
			}
		}.start();
	}
	
	
	/**
	 * 获取所有的省份
	 * @return
	 */
	public List<String> selectProvinces() {
		Cursor cursor = db.rawQuery("select distinct _province from " + ConstConfig.HOSPITAL_TABLE + " where _province is not null" 
	         + " union select distinct _province from " + ConstConfig.DOCTOR_TABLE + " where _province is not null " + " order by _province", null);
		LinkedList<String> provinces = null;
		if (cursor != null) {
			if (cursor.getCount() > 0 && cursor.moveToFirst()) {
				provinces = new LinkedList<String>();
				do {
					provinces.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		return provinces;
	}
	
	
	/**
	 * 获取某一省份下的所有城市
	 * @param province 省份
	 * @return
	 */
	public List<String> selectCities(String province) {
		Cursor cursor = db.rawQuery("select distinct _city from " + ConstConfig.HOSPITAL_TABLE + " where _province=? and _city is not null " + " union select distinct _city from " +
	           ConstConfig.DOCTOR_TABLE + " where _province=? and _city is not null" + " order by _city", new String[]{province, province});
		LinkedList<String> cities = null;
		if (cursor != null) {
			if (cursor.getCount() > 0 && cursor.moveToFirst()) {
				cities = new LinkedList<String>();
				do {
					cities.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		return cities;
	}
	
	
	/**
	 * 获取某一省份和市下的所有县
	 * @param province 省份
	 * @param city 城市
	 * @return
	 */
	public List<String> selectCounty(String province, String city) {
		Cursor cursor = db.rawQuery("select distinct _county from " + ConstConfig.HOSPITAL_TABLE + " where _province=? and _city=? and _county is not null" + " union select distinct _county from " +
	           ConstConfig.DOCTOR_TABLE + " where _province=? and _city=? and _county is not null" + " order by _county", new String[]{province, city, province, city});
		ArrayList<String> counties = null;
		if (cursor != null) {
			if (cursor.getCount() > 0 && cursor.moveToFirst()) {
				counties = new ArrayList<String>(cursor.getCount());
				do {
					counties.add(cursor.getString(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		return counties;
	}
	
	
	/**
	 * 查询新增、修改、删除的患者档案
	 * @return
	 */
	public List<LinkedHashMap<String, String>> selectPatientFileChangedData() {
		List<LinkedHashMap<String, String>> data = null;
		Cursor cursor1 = db.rawQuery("select * from " + ConstConfig.PATIENT_FILE_TABLE + " where _operation is not null and _operation>0 order by _operation asc,_cid asc,_id asc", null);
		if (cursor1 != null && cursor1.getCount() > 0 && cursor1.moveToFirst()) {
			data = new LinkedList<LinkedHashMap<String, String>>();
			do {
				LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
				map.put("local_id", cursor1.getString(0));  //数据库本地id
				map.put("realname", cursor1.getString(1));  //姓名
				map.put("sex", cursor1.getString(2));  //性别
				String birthDay = cursor1.getString(3);  //出生日期
				if (birthDay != null && birthDay.trim().length() > 0) {
					String[] birthDayArray = birthDay.split("-");
					if (birthDayArray != null && birthDayArray.length == 3) {
						birthDay = birthDayArray[0] + "-" + (birthDayArray[1].length() == 1 ? "0" + birthDayArray[1] : birthDayArray[1])
								+ "-" + (birthDayArray[2].length() == 1 ? "0" + birthDayArray[2] : birthDayArray[2]);
					}
				}	
				map.put("birthday", birthDay);
				
				String endDay = cursor1.getString(4);  //服务截止
				if (endDay != null && endDay.trim().length() > 0) {
					String[] endDayArray = endDay.split("-");
					if (endDayArray != null && endDayArray.length == 3) {
						endDay = endDayArray[0] + "-" + (endDayArray[1].length() == 1 ? "0" + endDayArray[1] : endDayArray[1])
								+ "-" + (endDayArray[2].length() == 1 ? "0" + endDayArray[2] : endDayArray[2]);
					}
				}
				map.put("fwjztime", endDay);  
				map.put("address", cursor1.getString(5));  //地址
				map.put("tel", cursor1.getString(6));   //电话
				map.put("mobile", cursor1.getString(7));  //手机
				map.put("email", cursor1.getString(8));  //邮箱
				map.put("constitution", cursor1.getString(9)); //体质
				map.put("blood_type", cursor1.getString(10));  //血型
				map.put("family_history", cursor1.getString(11));  //家族史
				map.put("personal_history", cursor1.getString(12));  //个人史
				map.put("disease_history", cursor1.getString(13));  //疾病史
				int cid = cursor1.getInt(14);  //服务端id
				if (cid > 0) {
					map.put("id", String.valueOf(cid));
					map.put("action", "modify");
				} else {
					map.put("action", "add");
				}
				map.put("hf", cursor1.getString(16));  //婚否
				map.put("lxr", cursor1.getString(17));  //联系人
				map.put("lxrtel", cursor1.getString(18));  //联系人电话
				map.put("ywgms", cursor1.getString(19));  //药物过敏史
				data.add(map);
			} while (cursor1.moveToNext());
		}
		if (cursor1 != null) {
			cursor1.close();
		}
		
		Cursor cursor2 = db.rawQuery("select _id,_cid from " + ConstConfig.DELETED_PATIENT_FILE_TABLE + " order by _cid asc", null);
		if (cursor2 != null && cursor2.getCount() > 0 && cursor2.moveToFirst()) {
			if (data == null) data = new LinkedList<LinkedHashMap<String, String>>();
			do {
				int cid = cursor2.getInt(1);
				if (cid > 0) {
					LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
					map.put("local_id", cursor2.getString(0));  //数据库本地id
					map.put("id", String.valueOf(cid));  //服务端id
					map.put("action", "delete");
					data.add(map);
				}
			} while (cursor2.moveToNext());
		}
		if (cursor2 != null) {
			cursor2.close();
		}
		
		return data;
	}
	
	
	/**
	 * 查询新增、修改、删除的病历资料
	 * @return
	 */
	public List<LinkedHashMap<String, String>> selectDiseaseRecordChangedData() {
		List<LinkedHashMap<String, String>> data = null;
		Cursor cursor1 = db.rawQuery("select * from " + ConstConfig.DISEASE_RECORD_TABLE + " where _operation is not null and _operation>0 order by _operation asc,_patient_id asc,_cid asc,_id asc", null);
		if (cursor1 != null && cursor1.getCount() > 0 && cursor1.moveToFirst()) {
			data = new LinkedList<LinkedHashMap<String, String>>();
			do {
				LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
				map.put("local_id", cursor1.getString(0));  //数据库本地id
				
				String pid = cursor1.getString(1);  //患者档案id
				int cpid = 0;  //患者档案服务端的id
				Cursor cursor = db.rawQuery("select _cid from " + ConstConfig.PATIENT_FILE_TABLE + " where _id=" + pid, null);
				if (cursor != null && cursor.moveToFirst()) {
					cpid = cursor.getInt(0);
				}
				if (cursor != null) {
					cursor.close();
				}
				if (cpid <= 0) {
					continue;
				}
				map.put("pid", String.valueOf(cpid));   //患者档案服务端的id
				
				String startTime = cursor1.getString(2); //开始时间
				if (startTime != null && startTime.trim().length() > 0) {
					String[] startTimeArray = startTime.split("-");
					if (startTimeArray != null && startTimeArray.length == 3) {
						startTime = startTimeArray[0] + "-" + (startTimeArray[1].length() == 1 ? "0" + startTimeArray[1] : startTimeArray[1])
								+ "-" + (startTimeArray[2].length() == 1 ? "0" + startTimeArray[2] : startTimeArray[2]);
					}
				}	
				map.put("stime", startTime);
				
				String endTime = cursor1.getString(3); //结束时间
				if (endTime != null && endTime.trim().length() > 0) {
					String[] endTimeArray = endTime.split("-");
					if (endTimeArray != null && endTimeArray.length == 3) {
						endTime = endTimeArray[0] + "-" + (endTimeArray[1].length() == 1 ? "0" + endTimeArray[1] : endTimeArray[1])
								+ "-" + (endTimeArray[2].length() == 1 ? "0" + endTimeArray[2] : endTimeArray[2]);
					}
				}	
				map.put("etime", endTime);
				
				map.put("disease", cursor1.getString(4));  //疾病
				map.put("hospital", cursor1.getString(5));  //医院
				map.put("content", cursor1.getString(6));  //内容
				
				String pic = cursor1.getString(7);
				if (pic != null && pic.trim().length() > 0) {
					map.put("pic", pic);
				}
				
//				map.put("remindtime", cursor1.getString(8));
				map.put("jkfx", cursor1.getString(9));  //健康分析
				map.put("jkzd", cursor1.getString(10));  //健康指导
				map.put("jkts", cursor1.getString(11));  //健康提示
//				map.put("alarmid", cursor1.getString(12));
				map.put("department", cursor1.getString(13));  //科室
				map.put("doctor", cursor1.getString(14));  //主治医生
				map.put("doctor_tel", cursor1.getString(15));  //医生电话
				
				int cid = cursor1.getInt(16);
				if (cid > 0) {
					map.put("id", String.valueOf(cid));  //服务端id
					map.put("action", "modify");
				} else {
					map.put("action", "add");
				}
				
				String time = cursor1.getString(18); //结束时间
				if (time != null && time.trim().length() > 0) {
					String[] timeArray = time.split("-");
					if (timeArray != null && timeArray.length == 3) {
						time = timeArray[0] + "-" + (timeArray[1].length() == 1 ? "0" + timeArray[1] : timeArray[1])
								+ "-" + (timeArray[2].length() == 1 ? "0" + timeArray[2] : timeArray[2]);
					}
				}	
				map.put("jztime", time);  //就诊时间
				map.put("zs", cursor1.getString(19));  //主诉
				map.put("xbs", cursor1.getString(20));  //现病史
				map.put("tgjc", cursor1.getString(21));  //体格检查
				map.put("fzjc", cursor1.getString(22));  //辅助检查
				map.put("cbzd", cursor1.getString(23));  //初步诊断
				map.put("cf", cursor1.getString(24));  //处方
				map.put("yz", cursor1.getString(25));  //医嘱
				data.add(map);
			} while(cursor1.moveToNext());
		}
		if (cursor1 != null) {
			cursor1.close();
		}
		
		Cursor cursor2 = db.rawQuery("select _id,_cid from " + ConstConfig.DELETED_DISEASE_RECORD_TABLE + " order by _cid asc", null);
		if (cursor2 != null && cursor2.getCount() > 0 && cursor2.moveToFirst()) {
			if (data == null) data = new LinkedList<LinkedHashMap<String, String>>();
			do {
				int cid = cursor2.getInt(1);
				if (cid > 0) {
					LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
					map.put("local_id", cursor2.getString(0));  //数据库本地id
					map.put("id", String.valueOf(cid));  //服务端id
					map.put("action", "delete");
					data.add(map);
				}
			} while (cursor2.moveToNext());
		}
		if (cursor2 != null) {
			cursor2.close();
		}
		
		return data;
	}
	
	
	
	
	/**
	 * 下载患者档案
	 * @param account 帐号
	 * @param pwd 密码
	 * @return 成功的数据数量或是出错信息
	 */
	public String downLoadPatientFile(String account, String pwd) {
		//需要忽略插入的患者档案
		LinkedList<Integer> ignoreIds = null;
		Cursor cursor = db.rawQuery("select _cid from " + ConstConfig.PATIENT_FILE_TABLE + " where _cid is not null and _cid>0 "
				+ "union select _cid from " + ConstConfig.DELETED_PATIENT_FILE_TABLE + " where _cid is not null and _cid>0", null);
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				ignoreIds = new LinkedList<Integer>();
				do {
					ignoreIds.add(cursor.getInt(0));
				} while (cursor.moveToNext());
			}
			cursor.close();
		}
		
		int maxId = getMaxId(ConstConfig.PATIENT_FILE_TABLE);
		
		int page = 0;  //页数
		int count = 0; //下载并插入数据库中的数据量
		String info = null;  //返回的信息
		ContentValues cv = new ContentValues();
		while (page >= 0 && page < Integer.MAX_VALUE) {
			if (!NetUtil.hasNetwork(MyApplication.getInstance())) {
				return String.valueOf(count);
			}
			page++;
			HttpPost post = new HttpPost(URLConfig.DOWNLOAD_PATIENT_DISEASE_URL + "?action=patient&account=" + account +
					"&pwd=" + pwd + "&page=" + page + "&serial_number=" + FileHelper.getImieStatus(MyApplication.getInstance()));
			DefaultHttpClient client = new DefaultHttpClient();
			try {
				HttpResponse response = client.execute(post);
				if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
					byte[] data = EntityUtils.toByteArray(response.getEntity());
					if (data == null || data.length <= 0) {
						break;
					}
					String result = new String(data, "UTF-8");
					if (result == null) {
						break;
					}
					result = result.trim();
					if ("不存在该账户".equals(result) || "平板序列号错误".equals(result) || "用户名或密码错误".equals(result)) {
						if (count <= 0) {
							info = "请输入正确的密码";
						}
						break;
					} else if ("账号已过有效期！请充值或者联系客服".equals(result)) {
						if (count <= 0) {
							info = "帐号已过有效期！请充值！\n充值电话：医师宝客服中心 18702537275 18702613050";
						}
						break;
					} else if ("账号未激活请注册".equals(result)) {
						if (count <= 0) {
							info = "帐号未激活，点击确定前往医师谷网站进行注册！\n如需帮助请联系医师宝客服中心 18702537275 18702613050";
						}
						break;
					}
					
					if (!result.startsWith("{") || result.length() <= 16) {
						break;
					}
					Gson gson = new Gson();
					DownLoadPatientBeanList list = gson.fromJson(result, DownLoadPatientBeanList.class);
					if (list == null || list.isEmpty()) {
						break;
					}
					int size = list.size();
					for (int i = 0; i < size; i++) {
						DownLoadPatientBean bean = list.getBean(i);
						int cid = bean.getId();
						String name = bean.getRealname();
						if (ignoreIds != null && ignoreIds.contains(cid)) {
							continue;
						}
						if (cid <= 0 || name == null || name.length() <= 0) {
							continue;
						}
						
						cv.clear();
						if (maxId <= 0) {
							cv.put("_id", 1001);
						}
						cv.put("_cid", cid);
						cv.put("_name", name);
						
						String sex = bean.getSex();
						if (sex != null && sex.length() > 0) {
							cv.put("_sex", sex);
						}
						
						String birthDay = bean.getBirthday();
						if (birthDay != null && birthDay.length() > 0) {
							cv.put("_birth_day", birthDay);
						}
						
						String endDay = bean.getFwjztime();
						if (endDay != null && endDay.length() > 0) {
							cv.put("_end_day", endDay);
						}
						
						String address = bean.getAddress();
						if (address != null && address.length() > 0) {
							cv.put("_address", address);
						}
						
						String phone = bean.getTel();
						if (phone != null && phone.length() > 0) {
							cv.put("_phone", phone);
						}
						
						String mobile = bean.getMobile();
						if (mobile != null && mobile.length() > 0) {
							cv.put("_mobile", mobile);
						}
						
						String email = bean.getEmail();
						if (email != null && email.length() > 0) {
							cv.put("_email", email);
						}
						
						String body = bean.getConstitution();
						if (body != null && body.length() > 0) {
							cv.put("_body", body);
						}
						
						String bloodType = bean.getBlood_type();
						if (bloodType != null && bloodType.length() > 0) {
							cv.put("_blood_type", bloodType);
						}
						
						String familyHistory = bean.getFamily_history();
						if (familyHistory != null && familyHistory.length() > 0) {
							cv.put("_family_hitory", familyHistory);
						}
						
						String personalHistory = bean.getPersonal_history();
						if (personalHistory != null && personalHistory.length() > 0) {
							cv.put("_personal_hitory", personalHistory);
						}
						
						String diseaseHistory = bean.getDisease_history();
						if (diseaseHistory != null && diseaseHistory.length() > 0) {
							cv.put("_disease_history", diseaseHistory);
						}
						
						String marriage = bean.getHf();
						if (marriage != null && marriage.length() > 0) {
							cv.put("_marriage", marriage);
						}
						
						String contacts = bean.getLxr();
						if (contacts != null && contacts.length() > 0) {
							cv.put("_contacts", contacts);
						}
						
						String contactsMobile = bean.getLxrtel();
						if (contactsMobile != null && contactsMobile.length() > 0) {
							cv.put("_contacts_mobile", contactsMobile);
						}
						
						String allergy = bean.getYwgms();
						if (allergy != null && allergy.length() > 0) {
							cv.put("_allergy", allergy);
						}
						
						long row = insertData(ConstConfig.PATIENT_FILE_TABLE, cv);
						if (row > 0) {
							if (maxId <= 0) {
								maxId = 1001;
							}
							count++;
						}
					}
				}
			} catch (ClientProtocolException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				page = -1;
				break;
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				page = -1;
				break;
			}
		}
		
		return info == null ? String.valueOf(count) : info;
	}
	
	
	
	/**
	 * 下载病历
	 * @param account 帐号
	 * @param pwd 密码
	 * @return 成功的数据数量或是出错信息
	 */
	public String downLoadDiseaseRecord(String account, String pwd) {
		//需要忽略插入的病历
		LinkedList<Integer> ignoreIds = null;
		Cursor cursor1 = db.rawQuery("select _cid from " + ConstConfig.DISEASE_RECORD_TABLE + " where _cid is not null and _cid>0 "
				+ "union select _cid from " + ConstConfig.DELETED_DISEASE_RECORD_TABLE + " where _cid is not null and _cid>0", null);
		if (cursor1 != null) {
			if (cursor1.moveToFirst()) {
				ignoreIds = new LinkedList<Integer>();
				do {
					ignoreIds.add(cursor1.getInt(0));
				} while (cursor1.moveToNext());
			}
			cursor1.close();
		}
		
		//患者档案服务端id与本地id的映射
		HashMap<Integer, Integer> patientIds = null;
		Cursor cursor2 = db.rawQuery("select _cid,_id from " + ConstConfig.PATIENT_FILE_TABLE + " where _cid is not null and _cid>0", null);
		if (cursor2 != null) {
			if (cursor2.moveToFirst()) {
				patientIds = new HashMap<Integer, Integer>();
				do {
					int cid = cursor2.getInt(0);
					int id = cursor2.getInt(1);
					patientIds.put(cid, id);
				} while (cursor2.moveToNext());
			}
			cursor2.close();
		}
		
		Random random = new Random();
		int page = 0;  //页数
		int count = 0; //下载并插入数据库中的数据量
		String info = null;  //返回的信息
		ContentValues cv = new ContentValues();
		while (page >= 0 && page < Integer.MAX_VALUE) {
			if (!NetUtil.hasNetwork(MyApplication.getInstance())) {
				return String.valueOf(count);
			}
			page++;
			HttpPost post = new HttpPost(URLConfig.DOWNLOAD_PATIENT_DISEASE_URL + "?action=case&account=" + account +
					"&pwd=" + pwd + "&page=" + page + "&serial_number=" + FileHelper.getImieStatus(MyApplication.getInstance()));
			DefaultHttpClient client = new DefaultHttpClient();
			try {
				HttpResponse response = client.execute(post);
				if (response.getStatusLine().getStatusCode() == HttpStatus.SC_OK) {
					byte[] data = EntityUtils.toByteArray(response.getEntity());
					if (data == null || data.length <= 0) {
						break;
					}
					String result = new String(data, "UTF-8");
					if (result == null) {
						break;
					}
					result = result.trim();
					if ("不存在该账户".equals(result)  || "平板序列号错误".equals(result) || "用户名或密码错误".equals(result)) {
						if (count <= 0) {
							info = "请输入正确的密码";
						}
						break;
					} else if ("账号已过有效期！请充值或者联系客服".equals(result)) {
						if (count <= 0) {
							info = "帐号已过有效期！请充值！\n充值电话：医师宝客服中心 18702537275 18702613050";
						}
						break;
					} else if ("账号未激活请注册".equals(result)) {
						if (count <= 0) {
							info = "帐号未激活，点击确定前往医师谷网站进行注册！\n如需帮助请联系医师宝客服中心 18702537275 18702613050";
						}
						break;
					}
					
					if (!result.startsWith("{") || result.length() <= 16) {
						break;
					}
					Gson gson = new Gson();
					DownLoadDiseaseRecordBeanList list = gson.fromJson(result, DownLoadDiseaseRecordBeanList.class);
					if (list == null || list.isEmpty()) {
						break;
					}
					int size = list.size();
					int index = 0;  //图片编号
					for (int i = 0; i < size; i++) {
						DownLoadDiseaseRecordBean bean = list.getBean(i);
						int cid = bean.getId();  //服务端id
						int patientCid = bean.getPid();  //患者档案服务端id
						int patientId = patientIds == null || !patientIds.containsKey(patientCid) ? -1 : patientIds.get(patientCid);  //患者档案本地id
						if (patientId <= 0 || cid <= 0 || patientCid <= 0) {
							continue;
						}
						if (ignoreIds != null && ignoreIds.contains(cid)) {
							continue;
						}
						
						cv.clear();
						cv.put("_cid", cid);
						cv.put("_patient_id", patientId);
						
						String disease = bean.getDisease();
						if (disease != null && disease.length() > 0) {
							cv.put("_disease", disease);
						}
						
						String startDay = bean.getStime();
						if (startDay != null && startDay.length() > 0) {
							cv.put("_start_day", startDay);
						}
						
						String endDay = bean.getEtime();
						if (endDay != null && endDay.length() > 0) {
							cv.put("_end_day", endDay);
						}
						
						String hospital = bean.getHospital();
						if (hospital != null && hospital.length() > 0) {
							cv.put("_hospital", hospital);
						}
						
						String content = bean.getContent();
						if (content != null && content.length() > 0) {
							cv.put("_content", content);
						}
						
						String healthAna = bean.getJkfx();
						if (healthAna != null && healthAna.length() > 0) {
							cv.put("_health_ana", healthAna);
						}
						
						String healthInstruct = bean.getJkzd();
						if (healthInstruct != null && healthInstruct.length() > 0) {
							cv.put("_health_instruct", healthInstruct);
						}
						
						String healthDirect = bean.getJkts();
						if (healthDirect != null && healthDirect.length() > 0) {
							cv.put("_health_direct", healthDirect);
						}
						
						String department = bean.getDepartment();
						if (department != null && department.length() > 0) {
							cv.put("_department", department);
						}
						
						String doctor = bean.getDoctor();
						if (doctor != null && doctor.length() > 0) {
							cv.put("_doctor", doctor);
						}
						
						String phone = bean.getDoctor_tel();
						if (phone != null && phone.length() > 0) {
							cv.put("_phone", phone);
						}
						
						String complain = bean.getZs();
						if (complain != null && complain.length() > 0) {
							cv.put("_complain", complain);
						}
						
						String time = bean.getJztime();
						if (time != null && time.length() > 0) {
							cv.put("_time", time);
						}
						
						String present = bean.getXbs();
						if (present != null && present.length() > 0) {
							cv.put("_present", present);
						}
						
						String physicalCheck = bean.getTgjc();
						if (physicalCheck != null && physicalCheck.length() > 0) {
							cv.put("_physical_check", physicalCheck);
						}
						
						String auxiliaryCheck = bean.getFzjc();
						if (auxiliaryCheck != null && auxiliaryCheck.length() > 0) {
							cv.put("_auxiliary_check", auxiliaryCheck);
						}
						
						String initialDiagnosis = bean.getCbzd();
						if (initialDiagnosis != null && initialDiagnosis.length() > 0) {
							cv.put("_initial_diagnosis", initialDiagnosis);
						}
						
						String prescription = bean.getCf();
						if (prescription != null && prescription.length() > 0) {
							cv.put("_prescription", prescription);
						}
						
						String doctorAdvice = bean.getYz();
						if (doctorAdvice != null && doctorAdvice.length() > 0) {
							cv.put("_doctor_advice", doctorAdvice);
						}
						
						
						String[] pic = new String[10];
						pic[0] = bean.getPic1();
						pic[1] = bean.getPic2();
						pic[2] = bean.getPic3();
						pic[3] = bean.getPic4();
						pic[4] = bean.getPic5();
						pic[5] = bean.getPic6();
						pic[6] = bean.getPic7();
						pic[7] = bean.getPic8();
						pic[8] = bean.getPic9();
						pic[9] = bean.getPic10();
						StringBuilder sb = new StringBuilder();
						Map<File, String> map = new HashMap<File, String>();
						for (String text : pic) {
							if (text != null && text.length() > 0) {
								File parentDirectory = new File(ConstConfig.DOWNLOAD_DISEASE_RECORD_IMG_FILE);
								if (!parentDirectory.exists()) {
									parentDirectory.mkdirs();
								}
								if (!parentDirectory.exists() || !parentDirectory.isDirectory()) {
									break;
								}
								//以系统时间加随机数字来命名图片
								int randomNumber = random.nextInt(100);
								String randomText = randomNumber < 10 ? "00" + randomNumber : "0" + randomNumber;
								File imgFile = new File(parentDirectory, String.valueOf(System.currentTimeMillis()) + randomText + index + text.substring(text.lastIndexOf(".")));
								map.put(imgFile, URLConfig.WEBSITE + "upload/case/" + text);
								sb.append(imgFile.getAbsolutePath());
								sb.append(ConstConfig.IMAGE_SPLIT);
								index = index + 1;
							}
						}
						int length = sb.length();
						if (length > 0) {
							sb.delete(length - ConstConfig.IMAGE_SPLIT.length(), length);
						}
						boolean downloadImgComplted = true;  //标识下载图片是否完成
						if (sb.length() > 0 && map.isEmpty()) {
							//有图片但是没有存放图片文件夹
							continue;
						} else if (sb.length() > 0 && !map.isEmpty()) {
							//有图片
							downloadImgComplted = downLoadImg(map);
							cv.put("_image", sb.toString());
						}
						if (!downloadImgComplted) {
							//下载图片出错
							continue;
						}
						long row = insertData(ConstConfig.DISEASE_RECORD_TABLE, cv);
						if (row > 0) {
							count++;
						}
					}
				}
			} catch (ClientProtocolException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				page = -1;
				break;
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				page = -1;
				break;
			}
		}
		
		return info == null ? String.valueOf(count) : info;
	}
	
	
	
	public boolean isOpen(){
		return db.isOpen();
	}
	
	
	public void close(){
		if (db.isOpen()) {
			db.close();
		}
		dbHelper.close();
		dbOperator=null;
	}
	
	
}
